Home > Pivot > Create > Multiple Sheets

Pivot Table From Multiple Sheets

Create a Pivot Table using data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures. Use Power Query, Excel VSTACK formula, Excel macro, or the hidden Pivot Table Wizard.

Author: Debra Dalgleish

excel pivot table from multiple sheets

How to Create Pivot Table from Multiple Sheets

To create a Pivot Table in Microsoft Excel, you can use data from multiple worksheets in a workbook, or from different workbooks.

First, I've listed 4 ways to create a pivot table from multiple sheets, so you can decide which method will work best for you.

1) Power Query

I recommend this method, if your version of Excel has either Power Query, or Get & Transform Data

  • Versions: Only for versions of Excel that support Power Query, or Get & Transform Data
  • Pivot Table: Creates a pivot table with all fields from source data, and full flexibility.
  • Instructions: Go to the Power Query section below, to see a video, and to get the link for step-by-step instructions

2) Excel VSTACK Formula

If you're using Excel 365, with all the latest Excel functions, you can use a VSTACK formula to combine data from multiple tables.

  • Versions: Versions of Excel with VSTACK function
  • Pivot Table: Creates a pivot table with all fields from source data, and full flexibility.
  • Instructions: Go to the Excel VSTACK Formula section below, for step-by-step instructions

3) Excel Macros

In the download section at the end of the page, you can get sample files with macros, to quickly combine data from multiple tables.

  • Versions: Work in most versions of Excel
  • Pivot Table: Creates a pivot table with only 4 fields, and limited flexibility.
  • Instructions: Go to the Excel Macros section below, for details

4) Pivot Table Wizard

If you have an older version of Excel, without Power Query, and you don't want to use macros, you can use this method, as a last resort.

  • Versions: Available in any version of Excel
  • Pivot Table: Creates a pivot table with only 4 fields, and limited flexibility.
  • Instructions: Go to the Pivot Table Wizard section below, to see a video, and step-by-step instructions.

Power Query

If you have a version of Excel that supports Microsoft's Power Query, you can use it to combine the data in two or more tables. When you use Power Query:

  • tables can have different structures, but should have some columns with identical headings, so data can be combined.
  • tables can be in the same workbook, or in different files

For example, in the screen shot shown below, East and West region data will be combined, even though one column is unique in each table

combine tables in Power Query

Video: Use Power Query to Combine Data

This video shows how to get started, and how to use the Power Query Editor. There are written steps on the Combine Tables with Power Query page.

To follow along with this video tutorial, you can get the Power Query sample file, in the Download section, at the end of this page.

Excel VSTACK Formula

If you're using Excel 365, with all the latest Excel functions, you can use a VSTACK formula to combine data from multiple tables.

Region Names

In the sample file, there are two sheets with tables, for the East and West regions.

In the last column of each table, there is a simple formula that adds the region name in each row.

  • East sheet formula is: ="East"
  • West sheet formula is: ="West"

This ensures that the region name can be used to filter the data later, when the two tables are combined in a pivot table.

West region table with formula column

Video: Combine Data with VSTACK Function

Use an Excel 365 VSTACK formula to combine data from multiple sheets in Excel. Next, name the range, then create pivot table from the combined data.

This video shows the steps, and there are written steps below the video.

Video Timeline
  • 00:00 Pivot Table from Multiple Sheets
  • 00:20 VSTACK Function
  • 00:52 VSTACK Formula
  • 01:21 Combined Data
  • 01:39 Named Range
  • 02:11 Add Pivot Table

Add VSTACK Formula

On a worksheet named Vstack, there is an Excel spill formula in cell A1:

=VSTACK(Table1[#Headers],Table1,Table2)

That formula returns a vertical stack from the contents of 3 ranges:

  • headers from Table1 (East)
  • data from Table1 (East)
  • data from Table2 (West)

All 3 ranges have the same number of columns, but different numbers of rows.

headers and tables combined with VSTACK formula

Name Combined Data Range

To use the combined data as a pivot table source, it's important to create a named range, based on the VSTACK formula cell.

To name the range, follow these steps:

  • On the VStack sheet, select cell A1
  • On the Excel Ribbon, go to the Formulas tab
  • Click In the Defined Names group, click the Define Name command
  • In the name box, type a one-word name - I used PivotData
  • In the Refers to box, where the address is showing, type a number sign at the end: =VStack!$A$1#
    • That creates a reference to the cell's spill range -- all the "spill" cells with the combined data.
  • Click the OK button, to complete the name

create named range for VSTACK formula cell spill range

Create a Pivot Table

Finally, to create a pivot table from the combined data, follow these steps:

  • On the VStack sheet, select cell A1
  • On the Excel Ribbon, go to the Insert tab
  • Click in the Tablea group, click the Pivot Table command
  • When the Pivot Table from table or range dialog box opens, clear the Table/Range box
  • Type the defined name that you created, e.g. PivotData
  • Leave the other settings as they are, and then click the OK button

Set Up the Pivot Table

A new sheet is added to the workbook, with a blank pivot table

The PivotTable Field List should appear at the right, and you can add fields to the pivot table layout.

In the screen shot below, I put Item in the Row area, Region in the column area, and Total in the Values area.

set up the pivot table

Excel Macros

In the download section at the end of the page, you can get sample files with Excel macros, to quickly combine data from multiple tables.

The macros were created by former Excel MVP, Kirill Lipin, to create a pivot table from multiple Excel files:

  • all stored in the same folder
  • all tables with identical column headings

There are notes in the Excel files, and you can read the details in this post on my Contextures Blog, Create a Pivot Table from Multiple Files.

table structure for multiple consolidation

Pivot Table Wizard

To create a pivot table from different sheets in a workbook, or from different workbooks, you can use the old Excel Pivot Table Wizard, which is hidden in newer versions of Excel.

The video in the next section shows all the steps, and there are written instructions below the video.

Limitations

Before you decide to use this method, here are two important limitations to keep in mind:

  1. This method only works if all of the lists have identical column structures.
  2. The pivot table layout has many limitations, compared to a normal pivot table

Video: Pivot Table from Multiple Sheets

To see how to create a pivot table from data on different sheets, using the Pivot Table Wizard, watch this short video. The written instructions, and the video timeline, are below the video.

Note: To see an older version of this video, where I used Excel 2007, go to the Multiple Sheets Video page.

Video Timeline

  • 0:00 Data on 2 Sheets
  • 0:24 Open PivotTable Wizard
  • 0:50 Select Sheet Ranges
  • 1:08 Page Field Settings
  • 1:29 Adjust the Pivot Table
  • 2:04 Show Sum
  • 2:15 Page Field

Use Pivot Table Wizard

To create a pivot table from data on different sheets, or in different workbooks, using the PivotTable and PivotChart Wizard, follow the steps below.

First - Check Your Data

This method only works if all of the data lists or tables have identical column structures.

  • Check all of the lists that you want to use in the pivot table, and adjust their column structure, if necessary

table structure for multiple consolidation

Start the Pivot Table

Follow the steps below, to open the Pivot Table Wizard:

  • First, select any cell on a worksheet - you don't need to select a cell in one of the lists
  • Next, to open the PivotTable and PivotChart Wizard, use this keyboard shortcut:
    • Press Alt+D, then tap the P key
    • In older versions of Excel, the wizard was listed on the Data menu, as the PivotTable and PivotChart Report command.
  • The PivotTable and PivotChart Wizard opens, showing Step 1 of 3.

Step 1 - PivotTable and PivotChart Wizard

In Step 1, follow these steps:

  • In the first section, Where is the data that you want to analyze?, select the Multiple consolidation ranges option (or use the keyboard shortcut, Alt+C)
  • In the second section, What kind of report do you want to create?, select the PivotTable option (or use the keyboard shortcut, Alt+T)
  • Then, click Next, to go to Step 2a

PivotTable and PivotChart Wizard

Step 2a - PivotTable and PivotChart Wizard

In Step 2a, follow these steps:

  • Under the question, How many page fields do you want?, select the option, I will create the Page Fields.
  • Then, click Next, to go to Step 2b

Step 2a in PivotTable and PivotChart Wizard

Step 2b - PivotTable and PivotChart Wizard

In Step 2b, follow these steps, to add the first range that you want to use for the pivot table:

  • At the top of Step 2b, click in the Range box
  • Next, click on Excel in the background
  • Go to the first data range, or the first table, that you want to use for the pivot table
  • Select all the cells in that data range, including all the heading cells and all the data cells
  • The range address will appear in the Range box, showing the sheet name, and an absolute reference to the cells.
  • Click the Add button, to add that range to the All Ranges list (do not click the Next button yet).
  • Next, repeat these steps to select and add the second table or second range
  • If needed, continue to add ranges that you want to include in the pivot table
  • After you add all the ranges, go to the instuctions below, for the Page Fields
Range Selection Tips
  • Instead of selecting a range on the worksheet, you can type the name of range, such as EastData.
  • If the source data is in a named Excel table, you can refer to that table, using its name and [#All]. For example: Table2[#All]

select ranges for multiple consolidation

Number of Page Fields

Creating Page Fields is optional, but setting up one or more page fields will allow you to filter the pivot table later, based on each range that you add to the pivot table.

In this example, we'll create one page field, so the data can be filtered by region - East, West or both.

  • For the question, How many page fields do you want?, select 1

Note: See the notes below, for an example of using more than one page field.

number of page fields

Page Field Item Labels

After you add each range, follow these steps, so create an item label for it, in the page field.

  • In the All ranges list, click on a range, to select it
  • In the Item labels section, click in the Field One box
  • Type a label that identifies the selected region.
    • Note: If you are using multiple pages, type a label for the selected range in each of the Field boxes

In the screenshot below, the range on the West sheet is selected, and the item label, "West" has been entered for that range.

page field item labels for multiple consolidation

Add All the Remaining Ranges

After the first range has been added, and you created page field labels for it:

  • Repeat the steps to add all the remaining ranges.
    • Be sure to include headings and all the data for each range
  • For each range, enter a page field label that will make it easy to identify later, in the page filter's drop down list.
    • In the screen shot below, the pivot table page filter shows East and West items.
    • number of page fields

Finally, after all the ranges have been entered:

  • Click the Next button, to go to Step 3.

Step 3 - PivotTable and PivotChart Wizard

The final step is to select a location for the PivotTable.

1) For the first question, choose where you want to put the pivot table:

  • New worksheet, OR
  • Existing worksheet

1b) If you selected Existing worksheet, follow these steps next:

  • Click in the location box, then click on Excel in the background
  • Go to the sheet where you want the pivot table
  • Click on the cell where you want the pivot table to start
  • The sheet name and address will appear in the location box

2) Finally, click the Finish button, to create the pivot table

number of page fields

Review the Pivot Table

A pivot table appears on the worksheet, with the following layout:

  • First field from source data ranges is in the Row area, with item names listed there
  • All other fields from the source data are in the Values area
  • All Value fields show a Count of items, even if the values in that field are numeric.

Tip: For suggestions on how to make this pivot table look better, and easier to use, continue down to the Clean Up the Pivot Table section below

pivot table from multiple consolidation ranges

Multiple Page Fields

Instead of using only one page field, you could create 2, 3 or 4 pages, if needed. Based on the data ranges that you're adding, how would you want to filter the pivot table later?

For example, perhaps you have 4 different sheets, with sales data from:

  • east and west regions
  • the past 2 years.

In that case, you could create 2 pages: Region and Year

Then, create 2 field labels for each range, so they can be filtered by region or year:

  • East 2021 - Region: East, Year: 2021
  • West 2021 - Region: East, Year: 2022
  • East 2022 - Region: West, Year: 2021
  • West 2022 - Region: West, Year: 2022

Clean Up Multiple Consolidation Pivot Table

All of the fields from the source data are included in the multiple consolidation pivot table, so you can remove some of them, and make a few other changes.

Remove fields that don't contain meaningful data

In this example, the Colour, Date, Price and Rep fields contain text, or numbers that are meaningless in this report, so they will be removed.

  1. Click the drop down arrow in the Column Labels heading
  2. Remove the check marks for fields that you want to remove.
  3. Click OK

    remove values from multiple consolidation pivot table

Change the Value Field Calculation

By default, the Values will show as Count, and you can change that to Sum, or another calculation.

NOTE: This will affect all of the Values -- they cannot be changed separately.

  1. Right-click one of the Values
  2. Point to Summarize Values By, and click on Sum.

change the value field calculation

Remove Grand Total for Rows

The Grand Total for Rows is meaningless in this report, because it is showing the total for unrelated items, so it should be removed..

  1. Right-click on the heading for the Grand Total for Rows
  2. Click Remove Grand Total.

remove grand total for rows

Change the Labels

In the pivot tables, generic fields are created -- Row, Column, Value and Page1. You can rename those fields, to make the pivot table easier to understand.

  1. Click on any label in the pivot table, and type a new label, then press Enter
  2. For example, click on the Page1 label, type Region, and press Enter

The labels have been changed in the screen shot shown below. The Column Labels heading was replaced by a space character.

remove values from multiple consolidation pivot table

Change the Layout

By default, the pivot table has the Compact Report Layout, and you can change that to Outline, so each Row field will be in a separate column. Then, move the Page field into the Row area, above the existing Row field.

  1. Select any cell in the pivot table
  2. On the Ribbon, under PivotTable Tools, click the Design tab.
  3. In the Layout group, click Report Layout, then click Outline Form
  4. In the PivotTable Field List, drag the Page1 field from the Filters area, into the Row area, above the existing Row field.
  5. Change the Row Field to Item, now that it is in a separate column.

remove values from multiple consolidation pivot table

Limitations of Multiple Consolidation

When you create a pivot table from multiple consolidation ranges, the pivot table has the following limitations:

  • Only one field can show its items in the Row area
    • You can't select a field for the row area -- the first column from the source data is automatically selected
  • All other fields are added to the Values area
    • Their names are shown across the top of the pivot table, as column headings
  • All value fields use the same calculation, such as Sum or Count.

To help you work within these limitations, I've put a few suggestions below.

Before You Build the Pivot Table

To get the best results from a multiple consolidation ranges pivot table, try these suggestions for optimizing your source data layout:

  • Rearrange your database columns, so the most important column is at the far left.
    • That column of data will become the Row values in the pivot table.
  • If there are columns that you don't want in the pivot table, move those to the far right in the source data.
    • Then, do not include those columns when selecting the data ranges for the pivot table.

After You Build the Pivot Table

To improve a multiple consolidation ranges pivot table, try these suggestions for optimizing its layout:

  • Remove any meaningless data, such as columns full of zeros, for text data.
  • values changed to Sum

  • By default, the values are shown as Count. Choose a different function, if applicable, such as Sum
    • NOTE: The selected function will be applied to all the Values
  • Change the Page field names, from Page1 to something meaningful
  • Move Page fields into the Row area, to group the data based on the page items
    • In the screen shot below, Region is a page field, and Item is the first column in the source data.

remove values from multiple consolidation pivot table

Download the Sample Files

1) Power Query: Get the Combine Tables With Power Query sample file. The zipped Excel file is in xlsx format, and does not contain any macros.

2) VSTACK Formula: Get the Combine Tables with VSTACK formula sample file. The zipped Excel file is in xlsx format, and does not contain any macros.

3) Excel Macros: Download the Excel Macros sample file. Select two or more files which have lists in an identical structure, and code in this workbook creates a pivot table or Excel table from all the data.

4) Pivot Table Wizard: Download the Pivot Table Wizard sample file. The zipped Excel file is in xlsx format, and the workbook does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Related Articles

Combine Tables with Power Query

Unpivot, Power Query

Pivot Table Introduction

Plan and Set Up a Pivot Table

Pivot Table Refresh

Pivot Table Source Data

 

 

Last updated: June 10, 2024 1:47 PM