This tutorial will show you how to consolidate multiple worksheets into one Pivot table using Microsoft Excel.
Most of the time, when you create a Pivot table in Excel, you’ll use a list or an Excel table. For example, you might have different worksheets (or workbooks) in your collection with data arranged differently, but you’ll still want to create a pivot table.
Arrange your data properly, and you can easily consolidate multiple worksheets into one Pivot table. In this tutorial, we will use the consolidating sample file. You can download it from here. The workbook contains four worksheets, and all those worksheets are data collections that you can use to combine into a Pivot Table.
On the “Team1” worksheet, you can see a rectangular data region starting in cell B3 and running down cell F7.
Don’t forget: your data must be in a perfect rectangle.
Here comes a non-rectangular data sample. If we were to have Product in cell B2 and Year in cell C2, the data values would not be perfectly rectangular. In this case, the Pivot table consolidation technique will not work.
Note: We need to ensure that there are no blank cells in our data table. This data summarizes yearly sales for 4 years (from 2020 to 2023); the years are along the top, and the row labels are along the side.
Consolidate Multiple Worksheets into one Pivot Table
By default, the Pivot Table Wizard is not available on the ribbon.
Let us start the Pivot table wizard using a keyboard shortcut. First, press Alt+D and P to open the Pivot Table Wizard dialog box. A summary of data tables before we consolidate the worksheets: Sames ranges, same shapes, and same labels are required to combine datasets into a pivot table. We will use four worksheets that contain similar data.
Select the “Team1” worksheet, and now we can open the Pivot Table Wizard tool to consolidate multiple worksheets into a pivot table.
Now choose the “Multiple consolidation ranges” option under the “Where is the data that you want to analyze” section, then choose “Pivot table” and click “Next”.
Page fields are fields used to filter a Pivot Table’s contents. Locate the “How many page fields do you want” section and choose: “I will create the page fields”. Click Next.
Now you can identify the ranges that you want to consolidate. To do that, click the collapse dialog button on the right side of the Range box.
Choose the collapse dialog button, and select the first set of cells. Those are on the “Team1” worksheet. Select from B2 to F7. Next, expand the dialog box, and then click “Add“.
Next, click the “Team2” tab, select cells B3 to F7, expand the dialog box, and click Add. Finally, repeat these steps for the “Team3” and “Team4” worksheets.
We have identified the consolidation ranges. The next task is to specify how many page fields we want. In this example, select 0. Click “Next” to consolidate multiple Worksheets into a new pivot table.
Finally, click “Finish”.
Consolidate Multiple Worksheets and use the drill-down method
Using a drill-down method, you can create an Excel table based on this data. You first need to remove all field headers from the Rows and Columns area so that you are left with just the Grand Total in the Value area, so it should be a single cell, in this case, A2.
To create the Excel table, double-click that cell; in this case, it’s cell A2. Doing so creates an Excel table containing a list of your data. So now we have a list that contains the consolidated data.
We strongly recommend using our free Excel add-in, DataXL, if you are in a hurry. It’s not complex to consolidate multiple worksheets into a Pivot table and prepare data to create dashboard templates. We have to use the same shapes for data sets. Use the drill-down method to make your life easier.
Download the sample workbook.