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.
- Select all the cells you want to transpose
- Copy the cells (e.g. press Ctrl+C)
- Right-click on the cell where you want to put the transposed data
- Click Paste Special on the context menu
- Tick Transpose on the dialog which appears (it’s at the bottom right)
- Click OK
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:
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:
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.