“Show me a person who likes HLOOKUP and I will show you a person who doesn’t know about transpose in Excel,” quipped Mr. Excel (Bill Jelen) at Excelapalooza last fall.

Indeed, so many people are crazy about the VLOOKUP function, but you rarely hear anyone talk about VLOOKUP’s less popular cousin HLOOKUP.  This is partially because people tend to orient their spreadsheets vertically (as they should!). But also because, in the rare instance of a horizontally oriented list, the transpose feature in Excel has you covered. Transpose will allow you to turn a horizontally oriented list into a vertically oriented list, or vice versa.

“Why haven’t I seen this transpose button?”

Well, it is not a button per se… (but it should be!). Excel has packed some sneaky features into their paste options. This is just one of them (more on this later).

Exercise File

If you would like to follow along, download today’s exercise here: TransposeExercise

On this sheet is a list of letter grades and GPAs. It is horizontally oriented, and I would prefer to see it vertically displayed.

Transpose: Paste Special

1. Select cells D1 through P2 (all the data).
2. Copy the data to your clipboard, either by pressing ctrl C or right click, Copy.
3. Select cell A1. This is the beginning of where you will paste your data.
4. In the Clipboard group of the Home tab, select the Paste dropdown.
   
5. Select Paste Special.
6. Check the box next to Transpose and press OK.

And there is your data, now vertically oriented!

It is worth reiterating that this is just the beginning of paste options in Excel. More on this at a later date…

I hope this feature saves you some time and frustration with horizontally oriented lists. Sometimes it is these small but mighty hidden check boxes in Excel that make all the difference!

 

Congratulations, New Power Users!

• Sandy Parker