Video: How to Convert Columns to Rows in Excel

This video is the complement to the How to Switch Rows and Columns in Excel tutorial.


Video Transcript

Every now and then you’ll get an Excel spreadsheet where the layout isn’t the one that is the best for your needs. In this example, you can see that I’ve got company names going across the columns here, but then I have the data attributes that I would probably want to use for comparison going down the side. The current layout works, but it makes it harder for me to filter columns and to do sorting.

Instead, I would like to use a function in Excel called Transpose. You can think of it like flipping or switching the columns and rows. You can either follow along with this spreadsheet example, or you could use your own spreadsheet. That’s up to you.

Step one. Once I have the spreadsheet open, then what I need do is to click in the first cell. In this case, it’s cell A1.

Step two. Now I want to shift-click in the last cell. You’ll notice that I have a green outline around the range and the gray background.

Step three. The next thing that I want to do is go to the Home tab and choose Copy, or you can choose the shortcut keys Control + C, if you’re on a PC, or your Command + C if you’re on a Mac. And now what you’ll notice you have a moving dotted line around the range.

Step four. Now I would like to click the cell where I want to have the copied data appear. In this example, I’m staying on the same sheet, but I could also copy this to a different sheet.

Step five. I’m now going to right-click in the cell and go down to Paste options. And you can see I’ve got a series of icons. As I hover over each one, you’ll see the layout change based upon the icon. The one that I want is the fourth icon over.

Step six. I can just click that icon and then I can click off the range and you can see the data’s been copied, but it’s in a totally different layout. My company names are going down this way and I can make the column wider so that you can see a little bit better.

One other thing that you should remember is that this top data set and this bottom data set are independent of each other. I could delete all these rows and it would have no impact on what I just transposed down there. It will maintain the original formatting.