Today's podcast takes a solution found in "Pivot Table Data Crunching: Microsoft Excel 2010" by Michael Alexander and Bill Jelen. Taking a table that is currently set up as a Two-Way Table, Bill uses the 'Old Pivot Table Dialogue' in Excel to set up a Pivot Table to transform the current Table to a Linear Table. Follow along with Episode #1572 to learn how to access that 'Old Dialogue' and transform the Two-Way Table.
For more information on 'Pivot Table Tricks', Usage and general 'Pivot Table How-To', check out: ""Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen and Michael Alexander Pivot Table Data Crunching: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
For more information on 'Pivot Table Tricks', Usage and general 'Pivot Table How-To', check out: ""Pivot Table Data Crunching: Microsoft Excel 2010" by Bill Jelen and Michael Alexander Pivot Table Data Crunching: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel with MrExcel podcast: Two-Way Table to Linear.
Hey, check this out.
Today's episode is going to use a trick from Mike Alexander.
Mike Alexander is a co-author on “Pivot Table Data Crunching”.
I want to send a shout-out to Mike.
If you like this trick, check out Mike's book, available from Q.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Kamal.
Kamal says, “I have a two-way table”-- in other words, items going down the side and milestones going across.
He wants to know how to take that and change it into a linear table, so everything go on straight down.
This is a beautiful trick; Mike Alexander showed me this.
We're going to create a very special kind of PivotTable; one that's not here in this PivotTable dialog.
We have to go back to the old PivotTable dialog, which is Alt, D for data, P for PivotTable (Alt+D+P).
It's multiple consolidation range, click Next; I'm going to create the page fields—actually, I'm not going to create any, but I don't want them to create the page field.
Click next, select your data range.
There's only one data range and simply click Finish.
Alright.
And what we get is a PivotTable that looks exactly like the original data.
And at this point you're thinking, ”Wow, Mike, this was pointless”.
Okay, let's change it to make sure that we have a Sum.
So there's our dates, they're not formatted correctly, but I can change it here; although I really don't have to change it here, but-- ok, so there's our dates.
This is the exact same data we had to begin with.
I remember thinking, “Mike, what a horrible waste of time”; but I'm going to come here to the grand total; grand total and I'm going to double-click that cell.
Double-clicking gets us a view of what's in the PivotTable cash; and the multiple consolidation range PivotTable actually will unwind that data; so there's all the milestones and all the dates, exactly what Kamal was looking for.
An amazing trick.
I saw Mike do that, I said, “Wow, that is really, really cool”.
I'm glad he's on my topic.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel with MrExcel podcast: Two-Way Table to Linear.
Hey, check this out.
Today's episode is going to use a trick from Mike Alexander.
Mike Alexander is a co-author on “Pivot Table Data Crunching”.
I want to send a shout-out to Mike.
If you like this trick, check out Mike's book, available from Q.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Kamal.
Kamal says, “I have a two-way table”-- in other words, items going down the side and milestones going across.
He wants to know how to take that and change it into a linear table, so everything go on straight down.
This is a beautiful trick; Mike Alexander showed me this.
We're going to create a very special kind of PivotTable; one that's not here in this PivotTable dialog.
We have to go back to the old PivotTable dialog, which is Alt, D for data, P for PivotTable (Alt+D+P).
It's multiple consolidation range, click Next; I'm going to create the page fields—actually, I'm not going to create any, but I don't want them to create the page field.
Click next, select your data range.
There's only one data range and simply click Finish.
Alright.
And what we get is a PivotTable that looks exactly like the original data.
And at this point you're thinking, ”Wow, Mike, this was pointless”.
Okay, let's change it to make sure that we have a Sum.
So there's our dates, they're not formatted correctly, but I can change it here; although I really don't have to change it here, but-- ok, so there's our dates.
This is the exact same data we had to begin with.
I remember thinking, “Mike, what a horrible waste of time”; but I'm going to come here to the grand total; grand total and I'm going to double-click that cell.
Double-clicking gets us a view of what's in the PivotTable cash; and the multiple consolidation range PivotTable actually will unwind that data; so there's all the milestones and all the dates, exactly what Kamal was looking for.
An amazing trick.
I saw Mike do that, I said, “Wow, that is really, really cool”.
I'm glad he's on my topic.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
