Several ways to transpose Excel data with a formula. In this episode:
Paste Special Transpose does a snapshot of the data
TRANSPOSE function requires Ctrl+Shift+Enter
Using Ctrl+Shift+Enter with =2 off to the right
INDEX function with position
ROW(1:1) gives the number 1
It changes as you copy down
Combine INDEX and ROW
3rd method: Replace = with ^=
Paste Special Transpose does a snapshot of the data
TRANSPOSE function requires Ctrl+Shift+Enter
Using Ctrl+Shift+Enter with =2 off to the right
INDEX function with position
ROW(1:1) gives the number 1
It changes as you copy down
Combine INDEX and ROW
3rd method: Replace = with ^=
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1986 - Sideways with a Formula!
Hey, don't forget to subscribe to the playlist, I'll be podcasting the entire book.
Welcome back to MrExcel netcast, I'm Bill Jelen.
We need to take this data, that's going across, and make it go down.
Right, so the fancy word for that in Excel is to "transpose".
So CTRL+C there and then you can either right click and choose Transpose, or Paste Transpose, or Paste Special and choose Transpose, any of those will work, so there's the Transpose, and Paste Special.
Let's right click, this one here is transpose.
The problem with that though is, this is a one-time snapshot, right, this took the data as it was, and if these are formulas that are changing, well now we're out of sync, right.
So I did the copy, I did the paste, and now, these numbers have changed, but those are not the right range(?), alright?
So there's a couple of different ways to do this, and the first one is a bizarre formula, it's a function called transpose, transpose is one formula that's going to return many answers, 12 columns by two rows, so I have to choose 12 rows by two columns, you can look up there in the name box, to see how you've chosen, with all of that selected we're going to =TRANSPOSE, and point to the original array.
Now here's the most important part, you can't press Enter here, you have to do CTRL+Shift+Enter, CTRL+Shift+Enter, this creates an array formula, right, it's one formula returns many results.
It works great!
A couple of hassles, if you give this to someone who doesn't understand what array forms are, they might come in here, and edit something, and not know to press CTRL+Shift+Enter, remember, it's kind of a hassle.
In fact, you know, one of the things that, array formulas are great for, right, because no one understands them.
Let me just insert a worksheet here.
And just say, put instructions here: "Do NOT DELETE ANY OF THESE ROWS"!
Alright, and then you have stuff here.
And of course if you do this you know that, whoever you give the spreadsheet to is going to delete one of those.
Alright, so what I do is if I want to protect rows 3 through 12, I just come out here to the right, where no one can see out here, select 3 through 12 and build an array form, =2, CTRL+Shift+Enter, all right, and now because that array formula is sitting out there, when someone comes here and tries to delete one of these rows, "You can't change part of an array." Right, it'll prevent them from deleting it.
No one will understand what this means.
So our array formulas are amazing and, you know, hey, shout-out to Mike Gervin and his CTRL+Shift+Enter book, that covers array formulas.
If you want to, you know, just be amazing with Excel, read that book, but, for this simple little task here, although this works great, you know, there's problems.
Alright so, let's talk, about something.
The index function says "Here's a whole bunch of answers, and I want the comma first." Answer like that.
And that gets that 1%.
And then if I copy that down, and edit the formula, let's put the dollar signs in back up here, C2 to N1, we'll put dollar signs there, I'll press F4, copy that down, edit the formula, and go to 2, copy that down, edit the formula, go to 3...
Well this is really tedious, you know, a beautiful way to get the number 1 to change to 2, is to ask for the row of 1:1, and as I copy that down, it will automatically change to the column 2.
So the ROW function tells us what row something's in.
But this is just a great trick to make sure that we expand the range.
So over here, instead of asking for hard coding the one, I can ask for the row 1:1 like that.
And this, this is really good, this works reliably, you don't have to worry about anyone pressing CTRL+Shift+Enter, it's no(?) numbers up at the top change, things will change.
You know, still, index and row, it's a complicated formula.
I don't know, I would probably do this.
There is the third method I've seen pop-on, let's do this, we just simply build a regular old formula like this.
So now I have a simple array of formulas.
And then I use Find and Replace, CTRL+H change every occurrence of an = to ^= ! Seen a lot of different things there but ^ work.
Replace All, and that takes those formulas, and makes them not be formulas anymore.
Let's make it a little bit wider.
And now I'm going to copy that, CTRL+C and Paste Special, transpose, alright, and so now that brought those formulas down, and it didn't change them I brought them down.
And now CTRL+H again, and we change ^= to = and then, Replace All.
And now we have true live formulas, just simple little formulas, but I didn't have to build them one at the time.
This method, it's like once every three months, that I come up with some bizarre thing, where it's easier just to build the formulas, and then, you know, cut them and move them somewhere else.
And the ^ trick, it's a good, good trick to have in your arsenal.
Every now and then.
Hey, as I said, I'm going to be podcasting this whole book, it looks like it's going to take at least the whole month, maybe six weeks or so, but at the same time, you can buy the whole book now.
Click that "i" on the top-right hand corner, and go out, check out the book, "40 Excel Tips" plus a lot of other great stuff.
Alright, recap of this episode: Paste Special Transpose - it's great but it does a snapshot of the data, the original data is changing, it's not going to work.
The transpose function is great, works with the CTRL+Shift+Enter.
We talked about using CTRL+Shift+Enter with =2 , to prevent people from deleting rows, that's off to the right hand side.
INDEX function with position, but then you have to change the 1 to a 2 to a 3.
ROW(1:1) gives you the number 1, and it will change to 2 automatically.
So combine the INDEX and ROW.
And then the third method, replacing = with ^= !
Hey remember, the whole book was crowdsourced through people sending their favorite Excel tips, so Excel Ace and Tracia Williams suggested this feature so thanks to them.
And thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, don't forget to subscribe to the playlist, I'll be podcasting the entire book.
Welcome back to MrExcel netcast, I'm Bill Jelen.
We need to take this data, that's going across, and make it go down.
Right, so the fancy word for that in Excel is to "transpose".
So CTRL+C there and then you can either right click and choose Transpose, or Paste Transpose, or Paste Special and choose Transpose, any of those will work, so there's the Transpose, and Paste Special.
Let's right click, this one here is transpose.
The problem with that though is, this is a one-time snapshot, right, this took the data as it was, and if these are formulas that are changing, well now we're out of sync, right.
So I did the copy, I did the paste, and now, these numbers have changed, but those are not the right range(?), alright?
So there's a couple of different ways to do this, and the first one is a bizarre formula, it's a function called transpose, transpose is one formula that's going to return many answers, 12 columns by two rows, so I have to choose 12 rows by two columns, you can look up there in the name box, to see how you've chosen, with all of that selected we're going to =TRANSPOSE, and point to the original array.
Now here's the most important part, you can't press Enter here, you have to do CTRL+Shift+Enter, CTRL+Shift+Enter, this creates an array formula, right, it's one formula returns many results.
It works great!
A couple of hassles, if you give this to someone who doesn't understand what array forms are, they might come in here, and edit something, and not know to press CTRL+Shift+Enter, remember, it's kind of a hassle.
In fact, you know, one of the things that, array formulas are great for, right, because no one understands them.
Let me just insert a worksheet here.
And just say, put instructions here: "Do NOT DELETE ANY OF THESE ROWS"!
Alright, and then you have stuff here.
And of course if you do this you know that, whoever you give the spreadsheet to is going to delete one of those.
Alright, so what I do is if I want to protect rows 3 through 12, I just come out here to the right, where no one can see out here, select 3 through 12 and build an array form, =2, CTRL+Shift+Enter, all right, and now because that array formula is sitting out there, when someone comes here and tries to delete one of these rows, "You can't change part of an array." Right, it'll prevent them from deleting it.
No one will understand what this means.
So our array formulas are amazing and, you know, hey, shout-out to Mike Gervin and his CTRL+Shift+Enter book, that covers array formulas.
If you want to, you know, just be amazing with Excel, read that book, but, for this simple little task here, although this works great, you know, there's problems.
Alright so, let's talk, about something.
The index function says "Here's a whole bunch of answers, and I want the comma first." Answer like that.
And that gets that 1%.
And then if I copy that down, and edit the formula, let's put the dollar signs in back up here, C2 to N1, we'll put dollar signs there, I'll press F4, copy that down, edit the formula, and go to 2, copy that down, edit the formula, go to 3...
Well this is really tedious, you know, a beautiful way to get the number 1 to change to 2, is to ask for the row of 1:1, and as I copy that down, it will automatically change to the column 2.
So the ROW function tells us what row something's in.
But this is just a great trick to make sure that we expand the range.
So over here, instead of asking for hard coding the one, I can ask for the row 1:1 like that.
And this, this is really good, this works reliably, you don't have to worry about anyone pressing CTRL+Shift+Enter, it's no(?) numbers up at the top change, things will change.
You know, still, index and row, it's a complicated formula.
I don't know, I would probably do this.
There is the third method I've seen pop-on, let's do this, we just simply build a regular old formula like this.
So now I have a simple array of formulas.
And then I use Find and Replace, CTRL+H change every occurrence of an = to ^= ! Seen a lot of different things there but ^ work.
Replace All, and that takes those formulas, and makes them not be formulas anymore.
Let's make it a little bit wider.
And now I'm going to copy that, CTRL+C and Paste Special, transpose, alright, and so now that brought those formulas down, and it didn't change them I brought them down.
And now CTRL+H again, and we change ^= to = and then, Replace All.
And now we have true live formulas, just simple little formulas, but I didn't have to build them one at the time.
This method, it's like once every three months, that I come up with some bizarre thing, where it's easier just to build the formulas, and then, you know, cut them and move them somewhere else.
And the ^ trick, it's a good, good trick to have in your arsenal.
Every now and then.
Hey, as I said, I'm going to be podcasting this whole book, it looks like it's going to take at least the whole month, maybe six weeks or so, but at the same time, you can buy the whole book now.
Click that "i" on the top-right hand corner, and go out, check out the book, "40 Excel Tips" plus a lot of other great stuff.
Alright, recap of this episode: Paste Special Transpose - it's great but it does a snapshot of the data, the original data is changing, it's not going to work.
The transpose function is great, works with the CTRL+Shift+Enter.
We talked about using CTRL+Shift+Enter with =2 , to prevent people from deleting rows, that's off to the right hand side.
INDEX function with position, but then you have to change the 1 to a 2 to a 3.
ROW(1:1) gives you the number 1, and it will change to 2 automatically.
So combine the INDEX and ROW.
And then the third method, replacing = with ^= !
Hey remember, the whole book was crowdsourced through people sending their favorite Excel tips, so Excel Ace and Tracia Williams suggested this feature so thanks to them.
And thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
