How to create a pivot table from multiple sheets (data sources) in excel? From my previous blog, I have illustrated How to create pivot table in excel. The pivot table was using data from one single location. However, what about if the data itself is from multiple sources. Is it possible to create a pivot table with multiple data sources? How to integrate information into single pivot table?
You my consider to use functions like lookup function like vlookup or match and index or lookup to do integrate information into one single table before doing the pivot.
Yes, this may work if the amount of data is very small. But how about if we have million of rows? Using Vlookup or index and match function will simply increase the size of the excel spreadsheet, slow down your excel performance until it go over your acceptance level and close your excel application with the cost that losing everything you have done before apply those lookup formulas.
So what can we do?
Can we simply create a pivot table from multiple sheets (data sources)?
The answer is “Yes”. It is definitely possible. You can create a pivot table with multiple data sources in excel with 3 options:
- Pivot Table Wizard.
- Power Pivot
Pivot Table Wizard
This is easiest along the three. If all data is in the same file and they are already summarised in a cross-tab format, you can use the pivot table wizard to create a pivot able with multi-sources.
Therefore, following is the steps:
- For each data source if they are not in cross-tab format, summarise the data into cross-tab format using a pivot table.
- Use the Pivot Table Wizard to create a pivot table.
Suppose we have received a data file like this
Where data is split region into different tabs:
|West Region||Central Region|
|Eastern Region||South Region|
We want to get the sum of sales amount for each state and category in in single pivot table.
1. For each data source if they are not in cross-tab format, summarise the data into cross-tab format using a pivot table.
Following is the steps:
- Create a pivot table for each sheets where
- Row : State
- Column: Category
- Values : Sum of Sales
- Remove grand total and subtotal (if any)
- This is the pivot table
Now, we have summarised our data sources into cross-tab report format, and they are ready for the final pivot table
2. Use the Pivot Table Wizard to create a pivot table.
Just be reminded, the “Pivot Table” button from the insert ribbon can only be used to create pivot table with single data sources. It can not create a pivot table with multiple sources.
To create a pivot table with multiple sources, we need to use the “Pivot Table & Pivot Chart Wizard” .
By default, the “Pivot Table & Pivot Chart Wizard” is hidden from anywhere in excel ribbon or Quick Access Toolbar.
To access it, we need to add it to the Quick Access Toolbar or excel ribbon.
Please refer to my other blog How to add excel functions to Quick Access Toolbar to add the “Pivot Table & Pivot Chart Wizard” if it is not appeared to the Excel.
Following is the steps to create the final pivot table
(Assume the “Pivot Table & Pivot Chart Wizard” is available from the Quick Access Toolbar.)
- Open the “Pivot Table & Pivot Chart Wizard”
- Click “Multiple Consolidation Ranges”
- Select “I will create the page fields”
- Change number of page fields to “1”
- Add the range of the first pivot table to the pivot table
- Repeat step 4 until all tables are added. Click “Next” at the end.
- Select where should the new pivot table be placed and click “Finish”
- Excel will immediately create the pivot table for us.
- Change the value to “Sum” instead of “Count”
- Change the label of the pivot table accordingly
As the final pivot table is not directly referring to the actual data (the four sheets) and related to the corresponding pivot table report indeed, this pivot table is technically not related to those spreadsheet. Also, this pivot table will not be refreshed as long as those other pivot table refreshed. It must be refreshed after those individual pivot table being refreshed.
What is Cross-Tab Format
Following is the characteristic of data in Cross-Tab Format.
- Only one column in row area
- Only one row in column area
- The rest is value