Have you ever had data in one row, say a1:z1, and you need a formula that you can drag down and reference those cells? Or, have you ever had data in a1:z1 that you want to see vertically rather than horizontally? There are formulas you can write to accomplish this task, but the easy, down and dirt way, is to use transpose.

The spreadsheet below shows the result of transpose. The results in a4:b10 were taken from a1:g2.

 Excel Transpose

To do this yourself:

  1. Open a spreadsheet and dummy in data in cells a1:g2
  2. Select a1:g2, right click and select copy
  3. Select cell a4 and right click, choose “paste special”
  4. At the bottom of the paste special window is a transpose radio button, select it.
  5. Click OK

That is it! The data will now be presented vertically rather than horizontally. This is the best quick way of moving data and formulas horizontally to vertically and back.

Related posts:

  1. “Value” Excel Workbooks to lock in Values There are times when it is useful to remove all...
  2. #VALUE! Error in Microsoft Excel The #VALUE error is an error that is seen almost...
  3. Duplicates – Finding and Removing in Excel There are many occasions when you may need to remove...

This entry was posted on Thursday, October 23rd, 2008 at 11:51 am and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.