How to create a pivot table from multiple sheets (data sources) in excel using Pivot Table Wizard

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:

  1. Pivot Table Wizard.
  2. MS-Query
  3. 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:

  1. For each data source if they are not in cross-tab format, summarise the data into cross-tab format using a pivot table.
  2. Use the Pivot Table Wizard to create a pivot table.

Example.:

Suppose we have received a data file like this

Data - divided by region

Where data is split region into different tabs:

Data split by region into different sheets

West RegionCentral Region
West RegionCentral Region
East RegionSouth Region
Eastern RegionSouth 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:

  1. Create a pivot table for each sheets where
    1. Row : State
    2. Column: Category
    3. Values : Sum of Sales
  2. Remove grand total and subtotal (if any)
    1. click the pivot table
    2. Goto Design Ribbon and click “Gland Total”Pivot Table Design Ribbon
    3. Select “Off for Rows and Columns”Off for Rows and Columns
  3.  This is the pivot tablePivot Table for all data sources
    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” .

Setup:

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.)

  1. Open the “Pivot Table & Pivot Chart Wizard”
  2. Click “Multiple Consolidation Ranges”PivotTable and PivotChart Wizard - Step 1 - Multiple Consolidation Rages
  3. Select “I will create the page fields”
    PivotTable and PivotChart Wizard - Step 1 - Create Page Field
  4. Change number of page fields to “1”
    PivotTable and PivotChart Wizard - Page Field
  5. Add the range of the first pivot table to the pivot table
    1. Click Range
    2. Select the range of the pivot table
    3. Click “ADD”
      PivotTable and PivotChart Wizard - Add Range
    4. Enter the region name in Field onePivotTable and PivotChart Wizard - Enter Page Field Name
  6. Repeat step 4 until all tables are added. Click “Next” at the end.PivotTable and PivotChart Wizard - Click "Next"
  7. Select where should the new pivot table be placed and click “Finish”PivotTable and PivotChart Wizard - Select whereto put the new report
  8. Excel will immediately create the pivot table for us.
    Pivot Table - Multiple SourcePivot Table Field - Multiple Source Consolidation
  9. Change the value to “Sum” instead of “Count”
    1. Select “View Field Setting”Alternative Menu - Pivot Table Field
    2. Change the caluation method to “Sum”
  10. Change the label of the pivot table accordinglyFinal Pivot Table - Multiple Source using Pivot Table Wizard

Note

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.

Cross-Tab Table

  1.  Only one column in row area
  2. Only one row in column area
  3. The rest is value

Leave a Comment