Swap rows and columns in Excel

A quick spreadsheet tip here for Microsoft’s Excel. If you want to swap rows and columns, i.e. transpose the data, then you can do it using “Paste Special”. This is useful for turning a single row into a column (or vice versa), or transposing a whole table.

Step-by-step

  1. Select all the cells you want to transpose
  2. Copy the cells (e.g. press Ctrl+C)
  3. Right-click on the cell where you want to put the transposed data
  4. Click Paste Special on the context menu
  5. Tick Transpose on the dialog which appears (it’s at the bottom right)
  6. Click OK

Non-square selections

It’s important to remember that the width and height of your selection gets swapped over when you transpose it. This means that if your selection isn’t square (i.e. you have different numbers of rows and columns) then it won’t fit back in its original location, and you may see an error message if you try to do that. The solution is to paste the transposed data somewhere else (e.g. into another sheet), adjust the original location to make room, and then copy the transposed data back over.

Example of transposing a table

Let’s imagine you’ve got a table containing people’s gender and age. Your data might initially look like this:

Name Gender Age
Frank Male 21
Mark Male 32
Susan Female 24
Arthur Male 25
Mary Female 30

If you transpose it then your data stay intact, but are rearranged so that each person’s information is in a column instead of a row:

Name Frank Mark Susan Arthur Mary
Gender Male Male Female Male Female
Age 21 32 24 25 30

Notice that we originally had 6 rows and 3 columns, but after the transposition this was swapped to 3 rows and 6 columns. Which way round you put the rows and columns is mainly an issue of aesthetics and readability. The data remains intact either way.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.