Consolidate Multiple Worksheets

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.

data set for consolidate multiple worksheets

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.

start the pivot table wizard

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

where is the data that you want to analyze pivot table Excel

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.

consolidate multiple worksheets create page field

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.

pivot table wizard add consolidate ranges

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.

add multiple ranges that you want to consolidate pivot

Finally, click “Finish”.

consolidated data in the pivot table

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.

remove pivot table fields

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.

consolidate multiple worksheet into an excel table

Final words

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.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.