Software

Quickly transpose Excel 2010 data using Paste

Transposing data is easy using the built-in Transpose feature, but there's another way. You can use Paste to transpose data!
You read the title right--the only thing that would make transposing easier would be a magic blink. To tell the truth, transposing data has never been difficult using Excel's built-in feature. There's also a second method via the Paste command and it takes just a few clicks. Let's walk through a simple example in Excel 2010:

  1. First, select the data (table, spreadsheet) that you want to transpose and copy it to the Clipboard using [Ctrl]+C. For instance, the example table (shown below) is in A1:E7.
  2. Select the top-left cell of the range you want to copy the transposed data to (A9 for instance).
  3. Click the Home tab (if necessary).
  4. Click the Paste dropdown in the Clipboard group.
  5. Click Transpose (the last icon on the second row).

That's it! Excel's Paste feature quickly transposes the data. You can also do this in Excel 2003. First, copy the data and select a blank cell. Then, choose Paste Special from the Edit menu, check the Transpose option, and click OK.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
kkredding
kkredding

What if Transpose is greyed out and not selectable?

gonturan
gonturan

in Excel 2010, if you want to do this from the keyboard, select the cells you want to transpose, ctrl-c (to copy), move to the first destination cell, then ctrl+alt+v, e, <return>

It may take some practice doing the ctrl-alt-v, but it is a great timesaver

stapleb
stapleb

Hi Susan, This is from memory, so that's why I said "A Question". When you paste in 2010 there are a number of icons that appear at the bottom right of the pasted area. I thought "Transpose" was one of those icons.

Aide
Aide

Good tip, thanks. Is there a similar shortcut to switching data between cells? For example, move the contents of A1 to D1 and, at the same time, move the contents of D1 to A1?

Editor's Picks