Transposing data and charts for a different perspective on your data

- In this worksheet we see a list of names in columns B, C, D, et cetera. Imagine this list having been setup when the organization was relatively small, and over time it's growing. Now we can certainly see all the information about Edward Hayes right here, and Erik Pratt right here and so on, we see what's happening here. But as we start to scroll rightward we see more and more names. This is going to become unwieldy the more names we add, not easy to read, and it certainly would have made a lot more sense to say, why don't we take, for example, all this employee name type information here and put it in a column instead of a row.

So imagine if we were putting this down in row 10, we'd see employee names Hayes, Edward; Mercado, David; Pratt, Erik; and so on down here, and then here we'd see the departments and so on. Now, after you've gone so far with this you're thinking I don't want to reinvent the wheel, I don't want to retype all this. We can simply take this data and transpose it. It's very similar to the same concept, if you are familiar with pivot tables you'll know, the word pivot means in fact, take a list, pivot it from the upper left corner.

And that doesn't represent a single action with pivot tables, by the way, but it simply reminds us of the concept, we'd like to take data that's in a column row layout and turn it into a row column layout. We like to see the data currently in column A, we like to see that across a row. So with the existing data highlighted simply copy it. You can press control c. Other techniques, of course, for copying. I'm going to go to cell A10. Always make sure that when you do transpose data that you do it outside the existing data.

We're not going to destroy or remove that data just yet in rows one through eight, but we're about to transpose it, which means in effect make a copy of it. And we can do this in a couple of different ways. One way is from the home tab, after we've done the copy, click the arrow under paste. As you slide over some of these options here one of them is actually called Transpose. And you can see already what's about to happen in row 10. I'll simply click that button, and zoom back a little bit and scroll as well, and we see what's happening.

We'll have to adjust some columns, but if I simply zoom back a bit, we can see the difference here. At the moment we're not trying to read that so much as suggest the idea. We've take this data that's in a column row layout and turned it into row column. Now you can say that either way, but just make sure you understand the concept, the idea. We've flipped this, so to speak, flipped it on its side. Now, zooming back in, I'm using control and the mouse wheel. Eventually what we would do, if things were looking pretty good we'll drag across columns A through H, double click any column boundary-- that'll make the columns wide enough-- and if it looks good, imagine what will happen over time now as we add new records.

We can easily view the data. Now, most people who are familiar with handling lists in Excel would probably start off this way anyway. In other words they would not have used the layout that we see up above, but nevertheless there are times when we want to transpose data. And if you find yourself in certain situations where you're laying out data kind of quickly, just getting it on the screen, setting it up, sometimes you'll realize, "Maybe I should have transposed this." Well, try it. See what it looks like. I'm not saying that every single time you transpose data that the new layout is going to be better.

Certainly it will at times, like we're seeing here. The only major change I would make in this list, and it's not that difficult, would simply be to take the employee names here and probably keep them left aligned. But even that's not a necessary step, it just tends to look better and is more common. Perhaps the same thing I could have done with department too. But this is a much easier list to work with. Eventually what we'd probably do is simply get rid of the top nine rows. So transposing data is pretty quick and easy. On the next worksheet over I've got a similiar list here.

This one is a little bit different. It has formulas over in column N that are tabulating the totals for the year, even though we don't have our other data. And here too, I think you can imagine the need to say, "I want to see, eventually, every month for every state, "I want to see these numbers." If we plug in the numbers out here as we will over time, the columns are gonna have to be wider, and maybe others need to see this monitor as I'm using it as well, so this is not going to work so well. Let's transpose the data. Just like before we'll highlight the data, press control c.

This time we'll use the shortcut. I'm going to go to cell A7 and press control alt v. That's the shortcut for paste special. And in the dialog box, here is transpose. Once again, we are not destroying the existing data, we're simply making a copy of it, and the data we currently see in row one, is about to appear in row seven, eight, nine, et cetera down column A. Transpose, OK. And you can see here, as we eventually do fill in the other numbers here, this will be readable very easily on the screen with sufficient size.

Here too. Clicking in the upper left corner to select the entire worksheet, dragging across columns B through E, we'll simply double click right here. So we've transposed the data. And here too, eventually, I think we can make a strong case for saying we don't want the old list in rows one through five, simply get rid of that and work off of our new list. The formulas that we see in column N off here to the right, for example this one, adding up the data in row two, has simply been transposed here. We see this in our transposed list right here, same result, it's adding the data in columns.

So the formula transposition takes place as well. We can also transpose charts. But we don't use the word transpose, even though I've put it in the name of the sheet here. If we'd like to create a chart quickly from this data, all we need to do is press alt F1. Now sometimes you will need to highlight the data specifically if there's adjacent data that you don't want to include in the chart, but if you want to pick up all data, if it's delineated by empty cells and worksheet borders, you need only click inside the data.

I'm going to press alt F1, and there's a chart. For a moment, don't need the title, I'll get rid of that. So, we can move this chart over a little bit as I'm about to do, and let's also shrink it from its upper left corner like this so we can see the data more clearly too, as well as the chart. We can see what's happened. Now, when a chart is selected, on the design tab, the contextual tab that appears when we have charts selected there's a choice called Switch Row/Column. We don't see the word transpose there. And the description of it could leave you scratching your head a little bit.

It says, "Swap the data over the axis." I wish they had used transpose, perhaps that would have been a little clearer. I recommend using this almost every time you create a chart. Not that you're going to necessarily get a better display of the data, but you're going to get a display that you might not have thought of. So as I click this button back and forth, you see what's happening. Right now the columns are clustered by month. Click this button, now they're clustered by state. What I will do sometimes, I think you should too at least consider the option, when you have a chart like this press control d, which means duplicate, and move the charts around a little bit.

Now we've got both of them, and for the moment they're looking the same. Switch row column on one of them and not the other. Now, I'm not necessarily suggesting that you will always want to have two charts, each one with a different transposition, but as we look at the data I think you can make the case that in some situations it would be appropriate to have both. And all we're really doing here is transposing the chart in the same way that we might with data. Now, oddly enough, if the data had been transposed and we had created a chart, it would just look like the very first chart that we created.

In the background Excel has some kind of an algorithm that analyzes how many rows and how many columns we have and then bases the chart on that. So if I had taken this data here and transposed it, as we did earlier with data, I'll press control c here, and then click right here, control alt v for paste special. Do a transpose, click OK. If I create a chart from this data it's gonna look exactly like the first chart that we created here with the data before it was transposed.

Almost sounds like double talk, but it isn't. I'm selecting this data here, by inference, just clicking in it. I'll press alt F1, and there's the chart. And from its corner I'll shrink it a little bit. So there's the chart we get when we're basing it on the data that we see behind it. Remember the states across the top, months down the side. But earlier when the months were across and the states down the side, we got the same kind of chart. But consider the option of transposing, even though we don't see that word with the switch row/column button found on the contextual design tab activated when we have a chart.

So we've seen different uses of the transpose concept, first with data, then with charts.