Create a Pivot Table using data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures.
To see how to create a pivot table from data on different sheets, watch this short video. The written instructions are below.
Note: To see the video transcript, go to the Multiple Sheets Video page.
To create a Pivot Table, you can use data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures.
However, you won't get the same pivot table layout that you'd get from a single range, as you can see in the screen shot below.
If possible, move your data to a single worksheet, or store it in a database, such as Microsoft Access, and you'll have more flexibility in creating the pivot table.
If combining your data isn't an option, this pivot table tutorial explains the steps to create a pivot table from multiple consolidation ranges, describes the limitations, and suggests workaround solutions.
A pivot table appears on the worksheet, with the first field in the Row area, and all of the other fields from the source data in the Values area, showing a Count.
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.
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.
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.
The Grand Total for Rows is meaningless in this report, because it is showing the total for unrelated items, so it should be removed..
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.
The labels have been changed in the screen shot shown below. The Column Labels heading was replaced by a space character.
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.
In this example, Item is the first column in the data source, and the pivot table row heading shows the item names. Remaining fields are shown in the column area.
To avoid the limitations of multiple consolidation ranges, you can combine the source data into a single table, using one of the following methods.
If you have a version of Excel that supports Microsoft's Power Query add-in, you can use it to combine the data in two or more tables. The tables can be in the same workbook, or in different files.
The tables can have different structures, and should have some columns with identical headings, in which the data can be combined. In this example, the East and West region data will be combined, and one column is unique in each table.
Go to the Combine Tables with Power Query page for written instructions, and the sample file.
To follow this video tutorial, go to the Combine Tables with Power Query page and download the sample file with East and West sales data.
If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data.
Union Query Macro -- Sheets in One File
Union Query Macro -- Data in Multiple Excel Files
In Excel, you can open the Microsoft Query tool, and write a SQL statement to create a Union query (full outer join) to combine multiple tables. Then, use the result as the pivot table's source data.
To see an example, download the Union Query sample files. It has a query that was built manually, and has a button to refresh the data.
With this solution, you'll end up with a normal pivot table, with none of the limitations. However, it's a bit tedious to set up, especially if you have more than a couple of tables.
You can read more about MS Queries here:
Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz.
Before you use the sample code, replace the sample sheet names with the sheet names in your workbook. For example, if your sheet names are "East" and "West", change this line of code:
to this
In the code, you can also change the location where the pivot table will be added. In the sample file, the TableDestination is set for the active sheet, in range A1.
Then, after you make those small changes, click the button on the worksheet, and a summary pivot table is automatically created.
If you need to combine data in multiple files, here are a couple of options, using macros provided by Excel expert, Kirill Lapin.
Pivot Table - The first example works on multiple files, which must have the data in identical structures, and you can read the instructions on my blog. To see Kirill's pivot table code, you can download the Pivot Workbooks example. The zipped folder that contains the Report.xls file, and the five sample data files. Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.
Pivot Table or Excel Table - Select two or more files which have lists in an identical structure, and the code in this workbook will automatically create a pivot table or Excel table from all the data. Read the details in blog post, Create a Pivot Table from Multiple Files. Click here to download the sample files.
Download the sample pivot table tutorial file
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: September 28, 2020 4:02 PM