Kristine asks if a pivot table can be created by combining data from many worksheets? The answer is yes, you can; but the data has to be structured in a particular format for this to work. Bill shows us how to do that today in Episode #1331
Note: for a different method using VBA, see: Creating a pivot table with multiple sheets
Note: for a different method using VBA, see: Creating a pivot table with multiple sheets
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL. Learn Excel from MrExcel Podcasts #1331.
Pivot From Many Sheets. Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.
Today's question is sent by Christene.
Christene wants to create a PivotTable from many different worksheets and this is possible if you happen to have worksheets that are all of a similar structure and require that you have only one label going along the right hand side – product and then labelled cities.
In this case going across the top.
And as I go from Q1 to Q2 you'll notice that the number of cities doesn't have to be the same.
Just the fact that there are cities out there doesn't have to be the same cities so Charlotte in Q1, Cleveland in Q2.
There can be a different list of cities but the point is there has to be the same type of data across the top of the worksheet and down the left hand side.
Ok, now this PivotTable is called the Model Book Consolidation Range.
It was there in 2003, so few people were using it.
They didn't even make it part of the insert PivotTable dialogue box in 2007 or 2010.
So, you have to get back to the old 2003 dialogue box.
To do that we gonna do Alt + D for data and then P for PivotTable there's the old dialogue box when choosing multiple consolidation range step 1 then we are into step 2 AF3 now choose - "I'm going to create the page fields".
We'll talk about page fields in a second.
And then now we are going to specify our four ranges in this case.
So I can do Ctrl + Shift + Down arrow, Ctrl + Shift + Right arrow.
We'll add that and say, hey, I want one page field, and this page field is going to be called Q1 to identify that all these records are Q1 records.
Next, we are going to click the reference button and go find the Q2 again Ctrl + Shift + Down, Ctrl + Shift + Right.
We'll add that and now the field 1 is going to be Q2 so type Q2 tab, right?
And just make sure you can see when you click on the first one it's Q1 and when you click on the second one its Q2.
Right.
Let's go to Q3 and click the reference button and Q3, here we are, Ctrl + Shift + Down, Ctrl + Shift + Right and add that one.
For this one it will be Q3 tab.
Alright! Next, reference button - click on Q4 Ctrl + Shift + Right, Ctrl + Shift + Down and add that one.
Let's make sure this one is Q4.
Okay, now I don't know why I'm paranoid – I always go check and make sure that all of those field 1's are correct and that looks good.
We can click next or finish.
Next just says "hey where you going to put it?" I always go to a new work sheet.
Good enough! Alright here's what we have - these Row, Column, Value and Page fields - those are the four fields you get.
Page fields has the four Quarters and although they put it in the Page field, it doesn't need to stay there at all.
I can have the four Quarters going across the Column labels, and then the Column labels could be going down the row labels and finally we could take the row labels and move those to the report filter.
And so now we can go in and look for one particular product and see the sales to all the cities that appear on any of the worksheets or any of the quarters.
Row labels and column labels! I don't know how many times I hate that format, come back here to show in tabular form.
Yeah, Oh jeez, now I have column in page 1 and that's not any better than Row labels and Column labels.
I probably actually wanna go back.
Let's rename this one the active field to be city or market whichever makes sense to you.
Up here on page 1 active field will name that to be Quarter and then up here in the row field that is Product.
Okay so Christene! Good question.
It is possible to do what you wanted to do provided your data happens to be in a very specific structure and then you're good to go.
If you have more columns though then you're pretty much going to have to get all those into one worksheet.
There is actually a great tip in Excel Gurus Gone Wild that shows you using VBA.
How to pace all that together I'll have to see if we can find in that workbook to another netcast on that subject.
Hey, I want to thank everyone for stopping by we will see you next time for another netcast from MrExcel.
Pivot From Many Sheets. Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.
Today's question is sent by Christene.
Christene wants to create a PivotTable from many different worksheets and this is possible if you happen to have worksheets that are all of a similar structure and require that you have only one label going along the right hand side – product and then labelled cities.
In this case going across the top.
And as I go from Q1 to Q2 you'll notice that the number of cities doesn't have to be the same.
Just the fact that there are cities out there doesn't have to be the same cities so Charlotte in Q1, Cleveland in Q2.
There can be a different list of cities but the point is there has to be the same type of data across the top of the worksheet and down the left hand side.
Ok, now this PivotTable is called the Model Book Consolidation Range.
It was there in 2003, so few people were using it.
They didn't even make it part of the insert PivotTable dialogue box in 2007 or 2010.
So, you have to get back to the old 2003 dialogue box.
To do that we gonna do Alt + D for data and then P for PivotTable there's the old dialogue box when choosing multiple consolidation range step 1 then we are into step 2 AF3 now choose - "I'm going to create the page fields".
We'll talk about page fields in a second.
And then now we are going to specify our four ranges in this case.
So I can do Ctrl + Shift + Down arrow, Ctrl + Shift + Right arrow.
We'll add that and say, hey, I want one page field, and this page field is going to be called Q1 to identify that all these records are Q1 records.
Next, we are going to click the reference button and go find the Q2 again Ctrl + Shift + Down, Ctrl + Shift + Right.
We'll add that and now the field 1 is going to be Q2 so type Q2 tab, right?
And just make sure you can see when you click on the first one it's Q1 and when you click on the second one its Q2.
Right.
Let's go to Q3 and click the reference button and Q3, here we are, Ctrl + Shift + Down, Ctrl + Shift + Right and add that one.
For this one it will be Q3 tab.
Alright! Next, reference button - click on Q4 Ctrl + Shift + Right, Ctrl + Shift + Down and add that one.
Let's make sure this one is Q4.
Okay, now I don't know why I'm paranoid – I always go check and make sure that all of those field 1's are correct and that looks good.
We can click next or finish.
Next just says "hey where you going to put it?" I always go to a new work sheet.
Good enough! Alright here's what we have - these Row, Column, Value and Page fields - those are the four fields you get.
Page fields has the four Quarters and although they put it in the Page field, it doesn't need to stay there at all.
I can have the four Quarters going across the Column labels, and then the Column labels could be going down the row labels and finally we could take the row labels and move those to the report filter.
And so now we can go in and look for one particular product and see the sales to all the cities that appear on any of the worksheets or any of the quarters.
Row labels and column labels! I don't know how many times I hate that format, come back here to show in tabular form.
Yeah, Oh jeez, now I have column in page 1 and that's not any better than Row labels and Column labels.
I probably actually wanna go back.
Let's rename this one the active field to be city or market whichever makes sense to you.
Up here on page 1 active field will name that to be Quarter and then up here in the row field that is Product.
Okay so Christene! Good question.
It is possible to do what you wanted to do provided your data happens to be in a very specific structure and then you're good to go.
If you have more columns though then you're pretty much going to have to get all those into one worksheet.
There is actually a great tip in Excel Gurus Gone Wild that shows you using VBA.
How to pace all that together I'll have to see if we can find in that workbook to another netcast on that subject.
Hey, I want to thank everyone for stopping by we will see you next time for another netcast from MrExcel.
