Consolidate Multiple Ranges into One PivotTable in Excel

You can create a PivotTable in Excel based on data ranges from worksheets in a workbook or other workbooks, provided the datasets have similar column structures.

This tutorial shows how to consolidate multiple ranges into one PivotTable in Excel.

How to Consolidate Multiple Ranges Into One PivotTable in Excel

Suppose we have the regional tablets sales data of a particular technology company in four worksheets of a workbook, as shown below:

We want to consolidate the regional sales data in the four worksheets into one PivotTable.

We use the following steps:

  1. Select any cell on the first worksheet.
  2. Open the PivotTable and PivotChart Wizard by doing the following:
  • Press the Alt key on the keyboard and release it to activate the Key Tips on the Ribbon.
Graphical user interface, application

Description automatically generated
  • Press the D key and release it. Notice the instruction on the Ribbon, “Continue typing from the menu key sequence from an earlier version of Office or press ESC to cancel.”
Graphical user interface, application, Word

Description automatically generated
  • Press the P key and release it to launch the PivotTable and PivotChart Wizard.
  1. On the PivotTable and PivotChard Wizard – Step 1 of 3 dialog box, select the Multiple consolidation ranges option and click Next.
  1. On the PivotTable and PivotChart Wizard – Step 2a of 3 dialog box, select the Create a single page field for me option and click Next.
  1. On the PivotTable and PivotChart Wizard– Step 2b dialog box, click the Range Selector button on the Range box, select the dataset on the first worksheet, including the header row, click the Range Selector button again to go back to the entire dialog box, and click the Add button.
Graphical user interface, text, application, email

Description automatically generated

Notice that the dataset’s reference on the first worksheet is added to the All ranges box on the dialog box.

  1. Repeat step 5 as you open the other three worksheets and add the data ranges on the worksheets to the All ranges box of the dialog box.
Graphical user interface, text, application, email

Description automatically generated

Note: You can click the Browse button on the dialog box if you want to open and add ranges from other workbooks

  1. Once all the data ranges have been added to the dialog box, click Next.
  2. On the PivotTable and PivotChart Wizard – Step 3 of 3 dialog box, select the New worksheet option to put the PivotTable report on a new worksheet and click Finish.
Graphical user interface, text, application

Description automatically generated

The multiple ranges from different worksheets are finally consolidated into one PivotTable report on a new worksheet:

Once the PivotTable is created, drag and rearrange the rows as needed. For example, if we want the Samsung Galaxy tablet to be on top of the list, we do the following:

  • Select cell A11 containing the item “Samsung Galaxy Tab S8 Ultra,” hover the cursor on the top edge of the cell until you see the drag-and-drop icon:
Graphical user interface, application, table, Excel

Description automatically generated
  • Press and hold down the left mouse button, drag to cell A5 and release the button when you see a dark green line.

The Samsung tablet now becomes the first on the list, as shown below:

To update the PivotTable with new data, right-click any cell in the PivotTable and choose Refresh on the shortcut menu.

Graphical user interface, application, table

Description automatically generated

Conclusion

This tutorial showed how to consolidate multiple ranges into one PivotTable in Excel using the PivotTable and PivotChart Wizard. We hope you found the tutorial helpful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.