One of the things that I have to do periodically and always forget how to do is converting rows of data to columns or columns of data to rows. This quite often occurs when I’m collecting data from different sources and want to put them all in the same format in Microsoft Excel.
From the 2007 version of MS Excel (I think), there has been a neat feature to enable you to do this. The reason that I always forget how to convert from columns of data to rows and vice-versa is because I expect it to be on the ribbon as a commonly used function but it is not. I always spend a few minutes searching the ribbon before I remember where the transpose feature is.
In fact, there is no one click function for transposing data although the process is very simple but it does take a few steps. Here are the steps to flip data from rows to columns (or vice-versa), which is known as transposing data.
- Highlight your data that you want to flip including the headers.
- Right-click and select “Copy” or Ctrl+C.
- Click on a cell where you want the top left corner of the data to be copied to.
- Right-click and select the “Transpose” icon
and then redistribute rows and column widths as required.
Transpose data example
Here’s a working example of some data from a sporting club that has some extra columns with formula for the total membership and a cumulative total.
Highlight the whole data area then right-click and select “Copy” (or Ctrl+C if you don’t want to right-click).
The go to where you want to paste the transposed data, in my case I chose a separate worksheet. Right-click and select the “Transpose” icon under the Paste Options menu.
The resulting copy retains cell formatting and flips formula around so that all still makes sense too. For example, B2+C2 in cell D2 becomes B2+B3 in cell B4.
If you would like to try it out using the example above, I have included the transpose membership data workbook example here.
I hope that helps someone who has been manually transposing data. I will have to remember to look at this post before spending time hunting around the Excel ribbon the next time I need to convert rows of data to columns or columns of data to rows.