Transposing Spreadsheets

Ever started creating a spreadsheet, only to realize that you've messed up and the rows should really be columns and vice versa? If you work with spreadsheets a lot, it probably happens more than you'd like. It seems like a small annoyance, but it takes a surprising amount of time to correct. Here's the fast way.

I personally tested this tip in both Microsoft Excel 2003 and LibreOffice Calc; information about Excel 2007 and 2010 was pulled from here.

 

In LibreOffice or Excel 2003 or below (if you have menu bars, this is you):

1. Copy the cells you want to transpose. Often this will be the whole spreadsheet if you just started, but sometimes you might only need part of it. Depending on your exact software, a dotted line may start moving around the copied region.
2. Move to a new blank cell below everything else in the spreadsheet. Don't worry, you can move it to wherever you want later. (For some reason, the option to transpose is occasionally deactivated if you try to paste into an area overlapping the current text.)
3. Choose Edit --> Paste Special.
4. Select the Transpose checkbox and paste.
5. Delete the incorrectly positioned data and move the transposed cells to where they belong.

In Excel 2007 or above (if you have a ribbon instead of menu bars, this is you):

1. Copy the cells you want to transpose. Often this will be the whole spreadsheet if you just started, but sometimes you might only need part of it. Depending on your exact software, a dotted line may start moving around the copied region.
2. Move to a new blank cell below everything else in the spreadsheet. Don't worry, you can move it to wherever you want later. (For some reason, the option to transpose is occasionally deactivated if you try to paste into an area overlapping the current text.)
3. Go to the Home tab of the ribbon, click the little arrow below the Paste button, and select Transpose.
4. Delete the incorrectly positioned data and move the transposed cells to where they belong.

—

Soren "scorchgeek" Bjornstad

 

If you have found an error or notable omission in this tip, please leave a comment or email me: webmaster@thetechnicalgeekery.com.

 

Copyright 2012 Soren Bjornstad.

Verbatim copying and redistribution of part or all of this article

is permitted, provided this notice is preserved.