Upendra asks how to transpose data while keeping a live link between the original data and the transposed data. A normal Paste Special Transpose will turn the data but there are no links. In today's podcast, two formulas that will solve the problem.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1751 - Transpose With a Formula!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question, sent by Upendra, has data up here in the yellow part, and wants to transpose this data into the green part.
Transpose meaning the things going across the columns go down the rows.
But he wants it to be a live link, so if something changes up here, then it changes down here.
And of course, the normal method of Paste and Transpose, CTRL+C, and then Paste, Paste Special, Transpose.
See, Paste Link is grayed out, it cannot set up formulas when you Paste Special, Transpose.
Yes, the data gets transposed, but a change here does not get reflected into the chain, into the data down here.
So I'm going to undo, CTRL+Z.
Here is the very complicated form that I use to solve this problem.
The INDEX function says "Hey, we have an array of values up here, our range of values up here," press F4, "and then in what row and column do we want to return".
What's actually reversed, for the row we're going to use the COLUMN function, I'm going to put A1 here.
And for the column I'm going to put the ROW function, A1) . Now, the fact that I'm using A1 here, that is just a very geeky way to return the number 1.
The beautiful thing about that is, as I copy to the right, it's going to ask for row 1 here, and row 2 here, row 3 here, row 4 here, row 5 here.
The A1 has nothing to do with the fact that the data is in A1, not at all!
I'm always going to use A1 because I want the number 1.
Some people use A:A here, some people will use 1:1 here.
That absolutely works and it's a little bit better than my method, but I always go this just because that's what I do.
Alright, so here we go, I will check that 13, will change to a 1000 here, and it changes there.
So it's a cool little formula, most people have never used the INDEX function before, it's possible you never used the COLUMN before and ROW before, so it seems very intimidating to use this, but it's just a great little trick to do the transpose.
Another way to go, tip of the hat to Mike Girvin here, in his "Ctrl+Shift+Enter" book, select the whole range, =TRANSPOSE of that original data, and then Ctrl+Shift+Enter to get all the results at once.
And this, again, will update as the original data updates.
But this is an array formula.
You can't insert any columns, can't insert any rows.
I like to go with just the plain old INDEX function with COLUMN and ROW.
Alright hey, I want to thank you for stopping by, I want to thank Upendra for sending that question in, see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1751 - Transpose With a Formula!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question, sent by Upendra, has data up here in the yellow part, and wants to transpose this data into the green part.
Transpose meaning the things going across the columns go down the rows.
But he wants it to be a live link, so if something changes up here, then it changes down here.
And of course, the normal method of Paste and Transpose, CTRL+C, and then Paste, Paste Special, Transpose.
See, Paste Link is grayed out, it cannot set up formulas when you Paste Special, Transpose.
Yes, the data gets transposed, but a change here does not get reflected into the chain, into the data down here.
So I'm going to undo, CTRL+Z.
Here is the very complicated form that I use to solve this problem.
The INDEX function says "Hey, we have an array of values up here, our range of values up here," press F4, "and then in what row and column do we want to return".
What's actually reversed, for the row we're going to use the COLUMN function, I'm going to put A1 here.
And for the column I'm going to put the ROW function, A1) . Now, the fact that I'm using A1 here, that is just a very geeky way to return the number 1.
The beautiful thing about that is, as I copy to the right, it's going to ask for row 1 here, and row 2 here, row 3 here, row 4 here, row 5 here.
The A1 has nothing to do with the fact that the data is in A1, not at all!
I'm always going to use A1 because I want the number 1.
Some people use A:A here, some people will use 1:1 here.
That absolutely works and it's a little bit better than my method, but I always go this just because that's what I do.
Alright, so here we go, I will check that 13, will change to a 1000 here, and it changes there.
So it's a cool little formula, most people have never used the INDEX function before, it's possible you never used the COLUMN before and ROW before, so it seems very intimidating to use this, but it's just a great little trick to do the transpose.
Another way to go, tip of the hat to Mike Girvin here, in his "Ctrl+Shift+Enter" book, select the whole range, =TRANSPOSE of that original data, and then Ctrl+Shift+Enter to get all the results at once.
And this, again, will update as the original data updates.
But this is an array formula.
You can't insert any columns, can't insert any rows.
I like to go with just the plain old INDEX function with COLUMN and ROW.
Alright hey, I want to thank you for stopping by, I want to thank Upendra for sending that question in, see you next time for another netcast from MrExcel!
