Horizontal to Vertical in Excel - 3 Different Ways

This tutorial will show you 3 ways to transpose data. You know, converting horizontal Excel data to vertical data, changing rows into columns, or switching between horizontal and vertical layouts. The reason for this Excel tutorial was that I had a client that had the responsibility of preparing data for a pivot table. However, the data they received was in the wrong format. Now it's true that there are other ways of accomplishing this task. But I thought if I went through 3 ways then the choice would be up to you.

Which transpose method should you use?

Quick answer: use Paste Special Transpose for a one-off change, use the TRANSPOSE function when the result should update with the source cells, and use Power Query when reshaping data is part of a repeatable reporting process.

Method number 1 - Copy and Paste

The copy and paste method is by far the most straightforward method of transposing data. If fact some of you may already be more that familiar with selecting text and using the paste special - transpose way of doing this. Obviously the downside to this is that it is not flexible. Should you want to change the source data you will have to go through this process again so if we were rating this I would give this a 4/10.

Method Number 2 - Using the TRANSPOSE function

The Transpose function is an array function that allows you to transpose data from horizontal to vertical and vertical to horizontal. Whereas this solution of solving this Excel problem is a little more flexible that the copy & paste solution of method 1 but because it's an array function it can limit where you would use this. In fact at the time of recording the video I didn't know how to solve the problem of #N/A appearing in cells that are outside the range of the formula. Now though I have solved the problem. It was actually pretty straight forward but you still have to remember to select the number of cells you want included in the formula. You also need to remember to hold down the Ctrl and Shift keys on the keyboard when you press enter. That's Right! the TRANSPOSE function is an array formula which means you have to select the number of cells that it applies to beforehand.

Method Number 3 - OFFSET()

My suggestion is that you get familiar with the OFFSET function. It's a really handy one to know if you want to make your Excel formulas a load more flexible. Rather that have me go through the various arguments of the OFFSET function you're more than welcome to view our offset function tutorial.