Reader SV wrote in to ask:
“How to I transpose a table, like copying and pasting a vertical column of cells to a horizontal row of cells?”
Great question!
Let’s say you have data that looks like this:
Then you decide that you’d rather have the dates running across the top, with each individual and the totals in their own rows.
What should you do? Manually retype everything? Turn your monitor on its side?
Excel actually makes this extremely easy. You can do it in a few seconds.
Simply highlight the data and copy it.
Then right-click where you want to paste it. I recommend doing this on a separate sheet. When you right-click, select Paste Special.
This will bring up the Paste Special dialog box. Then all you have to do is check the box for Transpose, and click OK.
This gives you results that look like this. The formatting’s a little ugly, but all the data is in the right place.
Let’s take 30 seconds to clean up that formatting:
- Bold and center the months.
- Add a row beneath the months.
- Label the annual total.
- Remove the borders from the annual totals.
- Add a row beneath the names.
- Put borders on the monthly totals.
- Wrap text on column A so we can read it, and center it vertically.
Now this looks a lot nicer.
So that’s how you transpose rows and columns. It’s super easy. It takes about 5 seconds if you don’t care about making it look pretty, and under a minute if you want to clean up the formatting.
If you have questions on how to do something in Excel, please submit them here.