It is difficult to create a pivot table when you have a time component stretching across the columns in your data set. In today's episode, an amazing trick in Excel 2007 to change four quarterly columns into four rows. Episode 705 will show the multiple consolidation ranges trick.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Wow! We spent the last couple of days fixing of this data set and we're close but we still have time, in this case quarters going across our columns and it is a disaster to create a pivot table from this.
Let me show you what happens.
We choose [ ok ], maybe I choose to have a report by region, but then I include my numeric fields Q1, Q2, Q3, Q4 because there are separate fields.
They're not anything that I can total.
I'd have to go into calculated fields and calculate a total for that.
Just a real, not a good way to create a pivot table, well.
There's an amazing trick, and I want to send a shout-out to Mike Alexander.
Mike is my co-author on pivot table data crunching, both the regular in 2007 edition and Mike showed me this amazing trick.
Let's just go through it, I'm going to insert a new column here, and it's called 'Key' and basically I'm going to take all of my fields in A, B and C and concatenate them together with an ampersand and between each field I'm going to put a semicolon.
So, I can break them back out, again later.
Alright! So, you see that we've taken all the data from A B and C and put it in one single column.
I'll double click the fill handle, to copy that down.
Well now, I'm going to create a pivot table from columns E through I but I'm not going to use the new pivot table interface.
I need the old pivot table interface and the best way to do that is [ alt D ] and then [ P ].
Here's the old pivot table interface.
I'm going to use multiple consolidation ranges, click [ next ].
I'll create the page fields, click [ next ], specify my data range, click [ add ] and then simply, click [ finish ].
Now, what I get is a pivot table. That is exactly identical to the original data set and you're saying boy this you know this is what's this?
Why do we even care?
Well, if we go all the way down to the grand total of the grand total.
There is an amazing trick, if you double click any cell in a pivot table, it will give you all the records that are behind that data and because we use multiple consolidation ranges when I double-click down here.
I get a brand new spreadsheet and look what happened.
Excel took my data, which was going across the sheet and made it go down the sheet.
So, where I used to have columns for Q1, Q2, Q3, Q4.
I now have four times as many rows, one for Q1 Q2 Q3 Q4 and what I have to do.
I'm going to insert a couple of columns and use data and text to columns, in order to split column A back out into three fields.
So, we have data that is delimited on the second tab.
I'm going to say, it's by a semicolon and just click [ finish ].
We now have Model, Region, District, The quarter and Revenue.
Broken out in two separate field.
It's ready to go ready to create a pivot table.
Well, next week, we're start to take a look at pivot tables in Excel 2007 and so this will be a perfect data set with which to start.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
you
I'm Bill Jelen.
Wow! We spent the last couple of days fixing of this data set and we're close but we still have time, in this case quarters going across our columns and it is a disaster to create a pivot table from this.
Let me show you what happens.
We choose [ ok ], maybe I choose to have a report by region, but then I include my numeric fields Q1, Q2, Q3, Q4 because there are separate fields.
They're not anything that I can total.
I'd have to go into calculated fields and calculate a total for that.
Just a real, not a good way to create a pivot table, well.
There's an amazing trick, and I want to send a shout-out to Mike Alexander.
Mike is my co-author on pivot table data crunching, both the regular in 2007 edition and Mike showed me this amazing trick.
Let's just go through it, I'm going to insert a new column here, and it's called 'Key' and basically I'm going to take all of my fields in A, B and C and concatenate them together with an ampersand and between each field I'm going to put a semicolon.
So, I can break them back out, again later.
Alright! So, you see that we've taken all the data from A B and C and put it in one single column.
I'll double click the fill handle, to copy that down.
Well now, I'm going to create a pivot table from columns E through I but I'm not going to use the new pivot table interface.
I need the old pivot table interface and the best way to do that is [ alt D ] and then [ P ].
Here's the old pivot table interface.
I'm going to use multiple consolidation ranges, click [ next ].
I'll create the page fields, click [ next ], specify my data range, click [ add ] and then simply, click [ finish ].
Now, what I get is a pivot table. That is exactly identical to the original data set and you're saying boy this you know this is what's this?
Why do we even care?
Well, if we go all the way down to the grand total of the grand total.
There is an amazing trick, if you double click any cell in a pivot table, it will give you all the records that are behind that data and because we use multiple consolidation ranges when I double-click down here.
I get a brand new spreadsheet and look what happened.
Excel took my data, which was going across the sheet and made it go down the sheet.
So, where I used to have columns for Q1, Q2, Q3, Q4.
I now have four times as many rows, one for Q1 Q2 Q3 Q4 and what I have to do.
I'm going to insert a couple of columns and use data and text to columns, in order to split column A back out into three fields.
So, we have data that is delimited on the second tab.
I'm going to say, it's by a semicolon and just click [ finish ].
We now have Model, Region, District, The quarter and Revenue.
Broken out in two separate field.
It's ready to go ready to create a pivot table.
Well, next week, we're start to take a look at pivot tables in Excel 2007 and so this will be a perfect data set with which to start.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
you
