Excel Tips Weekly

– [Instructor] In Excel, the word transpose is used in two different ways. You can transpose data, for example, the data on this worksheet called TRANSPOSE. We could transpose this in such a way that the months could appear, for example, in column A and Sales, Expenses, Profits across the top. So, we could transpose data. There’s also a function called TRANSPOSE, which has a similar yet distinctly different meaning. Let’s first approach this idea. I’m looking at this thinking, it probably would look better if we had put the months over in column A, and furthermore, I’m thinking of putting in the entire year, the remaining six months here. This is going to look a lot better if it’s oriented vertically. So, without destroying this, and first highlighting it, let’s copy this data. Either right-click Copy or Ctrl+C, or any other copying technique. And elsewhere, it could be a different sheet, could be nearby, we’ll put it nearby for the moment, I’m going to click in cell A8 and then go to Paste Special. You can get to Paste Special in a variety of ways. One way is to right-click, here are some options, we could go directly to Paste Special this way, and down to the very dialog box at the bottom, click Paste Special. In this dialog box is Transpose, we see it at the bottom. There’s no description of it as we slide over it. Click OK, and there’s the same data that we’re seeing from above, and there’s a formula in cell H4, also to the right and below it, that’s adding up data from the six cells to its left. That got transposed, not as a value, but as a formula also. Same number, 1615.3, is down here in cell B15, and what is this as I double-click? It’s a formula adding cells from above. So, question now is, which one do I keep? I’ll be the judge of that. Maybe I like this layout better. I like this feature a lot, and many times when I’m setting up a worksheet quickly and I’m just kind of throwing things out onto the screen, I stop and think about, would this data look better if it were transposed? Sometimes yes, sometimes no, but it’s easy to make this change. And if this were the starting point and you simply wanted to say or ask yourself, how might that look if it were oriented with the row columns switched? Well, if we transpose this, it will look like the layout that we saw up here. So, at different times, think out that idea as you’re working with certain kinds of data, and it doesn’t hurt to transpose data. Put it on a separate sheet possibly or put it nearby, you don’t want to wipe out the original data, but it’s worth a look. And this list off to the right, same general idea. You can imagine, sometimes you’re setting up a little list here, you’re tracking the data, it occurs to you after so many months, maybe, this certainly would’ve been better if it were oriented vertically. Same general idea. Highlight the data, how ‘about Ctrl+C this time? I’m going to transpose it right here. And you can get to Paste Special by way of a keystroke shortcut. Many people are familiar with Ctrl+V, meaning Paste, this is Ctrl+Alt+V. There’s that Paste Special dialog box. Transpose, OK. The same general idea as we saw before. Now, there is a function called TRANSPOSE, and it’s got limited use. It also is a special function that’s called an array function. Let’s imagine that what you might like to have is this same data here, or the data below it, either one, being represented elsewhere and in a transposed version, like a mirror image of this. Not quite a mirror image, but sort of like a mirror image. So, let’s select this data. As I drag across these cells, note what is happening up here in what we call the Name Box as I start to drag across. I’ll pause right there. 3Rx3C, three rows by three columns. Now it’s three rows by four columns, and so on. And the reason I’m saying let’s look at that is, we do want to remember that ratio. Four rows by nine columns. So, that’s highlighted, four-by-nine, let’s keep that highlighted and go to Sheet2, and we’re going to highlight, not four-by-nine, but nine-by-four. Nine rows by four columns. Equal transpose, left parenthesis, and we’ll jump back to that other sheet and click and drag across these cells, but instead of pressing Enter, I’ll press Ctrl+Shift+Enter. That’s what we do when we work with array formulas. It’s a long process to describe exactly how this is going to work, but as we press Ctrl+Shift+Enter, we’re essentially putting the same formula in all these cells, but they are linked to the other data. And notice, as I click on this cell, look in the Formula Bar. I’ll click on the next cell, and every one of these has exactly the same formula. There are special rules regarding array formulas well beyond the scope of this tip, but nevertheless, what we’ve got here is a set of numbers, like the other ones, obviously lined up differently, but they’re linked to the others. So, if I jump back to that first sheet, if this number needs to be adjusted, this should’ve been 177, one, seven, seven, I’ll press Enter, and what’s happening on the other sheet? It’s linked, therefore it is changing. Eventually, of course, we would format these, the comma button would work well, and so on. Notice back on the TRANSPOSE sheet, down here, this data, which is not linked, earlier it was copied and transposed, so these aren’t linked. So, different uses here of the idea of transposing data, first by way of an action, and then by way of a function.


Leave a Reply