Excel Tip of the Week: Transpose

This week’s topic is transposing data.

Transposing is taking a set of data and reorienting it from vertical to horizontal or vice versa. When you are working with multiple rows or columns, transposing will essentially switch your column headings to row headings and vice versa. Excel has a few ways to accomplish this and I’ll be showing you two of them today. The first way is via the Paste Special feature (building on last week’s post) and the other way is through a special function called TRANSPOSE (go figure).

Paste Special: Transpose
This is the simpler method to transpose data, but as you’ll see it is static and doesn’t allow for dynamic updating. Here are the steps to transpose the data in the example spreadsheet.

1. Download the example spreadsheet here.
2. Highlight the range B3:M3 (all the months) and then copy (Ctrl +C)
3. Move to cell C7
4. PC: From the “Home” menu in the “Clipboard” section click “Paste” then select “Transpose.” The icon looks like this:  (Keyboard shortcut Alt → H → V → T)
5. Mac: From the “Home” menu in the “Edit” section click “Paste” then select “Paste Special”. The “Transpose” option is in the bottom right of the dialogue box. Click OK.
6. Now the months which were listed horizontally are listed vertically.
7. The instructions for the advanced example are contained within the spreadsheet on the next tab.

Special note: Most times when I transpose data I also Paste Special: Values. To do this you have to open the Paste Special dialogue box (Keyboard Shortcut Ctrl + Alt + V). From there you can select “Values” and “Transpose” and Excel will do both simultaneously. (see image below)

TRANSPOSE Function
This formula is not for beginners. It requires you to follow some precise steps, otherwise it won’t work. Here are the steps:

1. Highlight cells E7:E18 (You always have to highlight the exact area of where you want the transposed info, otherwise you’ll see errors)
2. Type the following into Excel: =TRANSPOSE(B3:M3) DO NOT HIT THE ENTER BUTTON YET
3. The TRANSPOSE function is called an array formula, so in order to make it operate properly you have to hold down Ctrl and Shift while you hit enter. You have to do this, it is not an option.
4. Notice the curly brackets { } that Excel placed around the formula. These brackets indicate array formulas.
5. The result looks the same as the Paste Special method, but if you change one of the months it will also change in your transposed data. Try it out.
6. There are instructions for an advanced example on the following tab.