Using Excel Transpose Data Feature

by

I tend to use Excel for a lot of things, which means I work with a lot of Excel data. But if you are like me, as we get older, it's easy to forget some of the basics we've learned a while back, but don't use as often.

You can spend a lot of time building Pivot Charts and Tables to get data just the way you want it, typically migrating some columns into rows and taking some rows into columns. But you don't always need to go to all the effort of using the Pivot features to accomplish much of the same thing. 

What I mean by that is there is more than one way to change Column Data into Rows and Row Data into Columns.

Let's look at an example of a worksheet in Excel that has data organized in columns (shown below). 

Sorry about this looking so small, but it has to fit into our publication format. In reality, I actually cut out half the columns so that it appeared this size, otherwise it would have been totally too small to read.

Anyway, my point is simple, it's somewhat difficult to look at a chart made up of this many columns. Just think about how it would look if there were twice as many columns. What I need to do is turn my columns into rows and my rows into columns.

I can see this happening with QuickBooks Data if we looked at only the Income accounts and asked QuickBooks to display the Classes where the classes corresponded to the regions. We would get a few rows of Income accounts, and maybe a dozen or more regions as I had when I first started out.

So in this case, I want my Regions to be displayed in rows and my Quarters to appear as the columns. In other words, I want to rotate, which is what Excel calls "transpose," the data from columns to rows.

Like I said, this is simple, but sometimes we simply forget the feature exists, so here's how to use it.

1. Select the range of data you want to transpose, including the row and column labels, and then press Ctrl + C (this is the 'copy' function):

2. Right-click in the first cell where you want to paste your rotated data:

3. Select the Transpose paste option from the right-click menu. (How many times have you seen this option and not even remembered what it did?)

4. Excel immediately turns your columns into rows and your rows into columns:

5. I may need to do a little clean up with respect to my table boarders and column widths, but that's easily accomplished. When I am finished, my table is so much more readable and it fits within our publication format, too. After all, you can read it now:

Using the Excel Transpose feature is much easier and a lot less work than a pivot table if you don't need all the extra "bells and whistles" you can accomplish with pivots. Don't get me wrong, Pivot tables definitely serve a purpose – when you need them.

But whenever possible, I always try to KISS wherever data is concerned.

Just recently, I used this feature to turn column-after-column of Top 100 voting results into meaningful rows that could be computed with ease. Bet you wish I'd created my examples from that data, don't you? 

No such luck – it's still a secret.