Transpose Data (Static and Linked)

Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5

Transpose Data – Result is static

This is actually a simple trick of using Copy and Paster (Special, Transpose).  See? It can be done in a second. Excel Tip - Transpose Data - opening.gif

Nevertheless, as I mentioned in previous post, many people are not aware of this simple trick even though they work with Excel every day (they work in accounting field).  What they did was to move cell one by one… @_@

Here’s the step-by-step screenshot:

  1. Of course is to Copy the source tableexcel-tips-transpose-data
  2. On destination cell, right-click –> Click on the “Transpose” icon directly. (Note: The icon appears as the fourth icon under the Paste Options; and also under Paste Special Options:

    excel-tips-transpose-data1

  3. Just in case you like to go into the Paste Special menu:excel-tips-transpose-data-1-1
  4. OK.

This is the easiest way to transpose a table (rows to columns, columns to rows).  However the result is static, meaning the data do not change with the data in the source table.

Transpose Data – Result is dynamic, i.e. linked

In order to make a linked transposed table, we need formula.  There is a TRANSPOSE function indeed.

However there are few tricky points to use TRANSPOSE:

First, it is an array function, meaning you have to press Ctrl+Shift+Enter to input the formula.  Second, also the most tricky one, is you have to select an exact transposed area in order to transpose the content successfully.

E.g. If the source table is a range of 4×6, then you should select a range of 6×4 in order to make it work.  Imagine a table of 26×128, is it that easy to select a range of 128×26?  @_@

To tackle this cumbersome step, here’s my tip:excel-tips-transpose-data-2

  1. Do the Copy and Paste (Special, Transpose) as demonstrated before.
  2. Right after the Paste, while the resulted range is still highlighted, type =TRANSPOSE(array), then Ctrl+Shift+Enter

Now you have a transposed table that is linked to the source table.

Is it much easier?  🙂

Nevertheless, another tricky point about TRANSPOSE is you cannot edit part of the resulting table (array).  If you try to insert, remove, or simply delete a cell of the resulting table, you will see this error message:

excel-tips-transpose-data-2-1

This could be annoying to many users.  So let’s explore another way of doing the same without using the TRANSPOSE function.  What we need is just a few more steps that can be done within a minute indeed.

  1. Copy and Paste (Special, Link) to create a “helper” table

excel-tips-transpose-data-3

(Tip: you may do it on the right-click menu directly, look for the “Link” icon that is the rightmost icon under Paste option)

2. While the “pasted” table is still selected, press Ctrl+H to open the Find and Replace dialog box, Find=” and Replace with^=” (note: do not enter the quotation mark)

excel-tips-transpose-data-3-1

You should be able to see the result like this:excel-tips-transpose-data-3-2

3. Now do the Copy and Paste (Special, Transpose) step

excel-tips-transpose-data-3-3

4. Repeat the Find and Replace, but in reverse way.excel-tips-transpose-data-3-4

5. Here we go!

excel-tips-transpose-data-3-5

An animated GIF for easy reference:

excel-tips-transpose-data-end

As simple as this! 🙂

Advertisements
This entry was posted in Excel Tips, Formula and tagged , , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s