In today's "Learn Excel from MrExcel Podcast" David needs a formula that will set up live formulas that turn Data sideways. Episode #1352 provides the solution as Bill shows us a more efficient method for achieving the desired results. Learn Excel!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1352: Better Transpose.
Hey, all right today's question is sent in by David.
David needs to take some data and turn it on its side, which of course is paste special transpose, right.
So click on Edit and select paste special; that's Alt+E followed by S and then transpose or if you're in Excel 2007 you just go to paste and choose transpose.
2007 or this little icon 2010 but the problem is that does not set up a link between these.
It actually copied the formulas over which in this case because it is a range happens to be generating the wrong thing.
Any other formula it's still not going to work; so what we need is a way to get a true link between those two and typically I use Indirect or Index or something like that but there's a faster way to go.
The cells I want to copy are 4 columns by 2 rows.
I want to change those into 4 rows by 2 columns.
So I'm going to select a range that is 4 rows by 2 columns.
Type one formula, equal transpose and then choose the original range up here, close parentheses.
=TRANSPOSE(A1:D2) Now, I don't press Enter here.
I hold down Ctrl + Shift and then press Enter.
Check that out; it is now a live link between the two.
If something changes that data changes in the original formula and is copied down here, even to the point if I would change this to be Q1.5, which I realise makes no sense.
See, that updates as well.
So this is one big formula that is pointing back to that range.
Faster way in my opinion.
A little bit easier than using Index and a little bit easier than using Indirect.
A good, good way to go.
So thanks to David for sending that question in and thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel
Learn Excel from MrExcel podcast episode 1352: Better Transpose.
Hey, all right today's question is sent in by David.
David needs to take some data and turn it on its side, which of course is paste special transpose, right.
So click on Edit and select paste special; that's Alt+E followed by S and then transpose or if you're in Excel 2007 you just go to paste and choose transpose.
2007 or this little icon 2010 but the problem is that does not set up a link between these.
It actually copied the formulas over which in this case because it is a range happens to be generating the wrong thing.
Any other formula it's still not going to work; so what we need is a way to get a true link between those two and typically I use Indirect or Index or something like that but there's a faster way to go.
The cells I want to copy are 4 columns by 2 rows.
I want to change those into 4 rows by 2 columns.
So I'm going to select a range that is 4 rows by 2 columns.
Type one formula, equal transpose and then choose the original range up here, close parentheses.
=TRANSPOSE(A1:D2) Now, I don't press Enter here.
I hold down Ctrl + Shift and then press Enter.
Check that out; it is now a live link between the two.
If something changes that data changes in the original formula and is copied down here, even to the point if I would change this to be Q1.5, which I realise makes no sense.
See, that updates as well.
So this is one big formula that is pointing back to that range.
Faster way in my opinion.
A little bit easier than using Index and a little bit easier than using Indirect.
A good, good way to go.
So thanks to David for sending that question in and thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel
