Consolidate multiple worksheets into one PivotTable report

Consolidating data is a useful way to combine data from different sources into one report. For example, if you have a PivotTable report of expense figures for each of your regional offices, you can use a data consolidation to roll up these figures into a corporate expense report. This report can contain sales totals and averages, current inventory levels, and highest selling products for the whole enterprise.

What do you want to do?


Learn about consolidating multiple ranges

To summarize and report results from separate worksheet ranges, you can consolidate data from each separate worksheet range into a PivotTable report on a master worksheet. The separate worksheet ranges can be in the same workbook as the master worksheet or in a different workbook. When you consolidate data, you are assembling it so that you can more easily update and aggregate data regularly or as needed.

The resulting consolidated PivotTable report can have the following fields in the PivotTable Field List, which you can add to the PivotTable report: Row, Column, and Value. In addition, the report can have up to four page filter fields, called Page1, Page2, Page3, and Page4.

 Note   The PivotTable and PivotChart Wizard uses the term page field in the user interface. In Microsoft Office Excel 2007, a page field is now called a report filter field in other parts of the PivotTable user interface.

Setting up the source data

Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together. Do not include any total rows or total columns from the source data when you specify the data for the report. The following example shows four ranges in cross-tab format.


Sample data sources for PivotTable report consolidation


Page fields in data consolidations

Data consolidation can use page fields that contain items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) representing one or more of the source ranges. For example, if you're consolidating budget data from the Marketing, Sales, and Manufacturing departments, a page field can include one item to show the data for each department, plus an item to show the combined data. The following example shows a consolidated PivotTable report that has one page field and multiple items selected.


Sample Consolidated PivotTable Report with a page field.


Using named ranges

If the range of data is likely to change the next time that you consolidate the data (that is, the number of rows may change), consider defining a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for each source range in the separate worksheets. Then use those names when you consolidate the ranges in the master worksheet. If a source range expands, you can update the range for the name in the separate worksheet to include the new data before you refresh the PivotTable report.

Other ways to consolidate data

Excel also provides other ways to consolidate data that work with data in multiple formats and layouts. For example, you can create formulas with 3D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.), or you can use the Consolidate command (on the Data tab, in the Data Tools group).

For more information, follow the links in the See Also section.

Top of Page Top of Page

Consolidate multiple ranges

You can use the PivotTable and PivotChart Wizard to consolidate multiple ranges. In the wizard, you can choose between using no page fields, a single page field, or multiple page fields.

Consolidate data without using page fields

To combine the data from all the ranges and create a consolidation that does not have page fields, do the following:

  1. Click a blank cell in the workbook that is not part of a PivotTable report.
  2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

ShowTip

To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:

  1. Click the arrow next to the toolbar and then click More Commands.
  2. Under Choose commands from, select All Commands.
  3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

  1. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
  2. On the Step 2a page of the wizard, click I will create the page fields, and then click Next.
  3. On the Step 2b page of the wizard, do the following:
  1. For each cell range, click Collapse Dialog Button image to temporarily hide the dialog box, select the cell range on the worksheet, press Expand Dialog Button image, and then click Add.

ShowTip


If the cell range is in another workbook, open the workbook first to make it easier to make select the data.

  1. Under How many page fields do you want?, click 0, and then click Next.
  1. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

Consolidate data by using a single page field

To include a single page field that has an item for each source range, plus an item that consolidates all the ranges, do the following:

  1. Click a blank cell in the workbook that is not part of a PivotTable report.
  2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

ShowTip

To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:

  1. Click the arrow next to the toolbar and then click More Commands.
  2. Under Choose commands from, select All Commands.
  3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

  1. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
  2. On the Step 2a page of the wizard, click Create a single page field for me, and then click Next.
  3. On the Step 2b page of the wizard, do the following:
  • For each cell range, click Collapse Dialog Button image to temporarily hide the dialog box, select the cell range on the worksheet, and then press Expand Dialog Button image.

ShowTip

If the cell range is in another workbook, open the workbook first to make it easier to make select the data.


  1. Click Next.
  2. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

Consolidate data by using multiple page fields

You can create multiple page fields and assign your own item names for each source range. This lets you create partial or full consolidations; for example, one page field that consolidates Marketing and Sales apart from Manufacturing, and another page field that consolidates all three departments. To create a consolidation that uses multiple page fields, do the following:

  1. Click a blank cell in the workbook that is not part of a PivotTable report.
  2. To start the PivotTable and PivotChart Wizard, press ALT+D+P.

ShowTip

To add the PivotTable and PivotChart Wizard to the Quick Access Toolbar, do the following:

  1. Click the arrow next to the toolbar and then click More Commands.
  2. Under Choose commands from, select All Commands.
  3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

  1. On the Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next.
  2. On the Step 2a page of the wizard, click I will create the page fields, and then click Next.
  3. On the Step 2b page of the wizard, do the following:
  1. For each cell range, click Collapse Dialog Button image to temporarily hide the dialog box, select the cell range on the worksheet, press Expand Dialog Button image, and then click Add.

ShowTip

If the cell range is in another workbook, open the workbook first to make it easier to make select the data.


  1. Under How many page fields do you want?, click the number of page fields that you want to use.
  2. Under What item labels do you want each page field to use to identify the selected data range?, for each page field, select the cell range, and then enter a name for that range.

ShowExample

  • If you selected 1 under How many page fields do you want?, select each range, and then enter a different name in the Field One box. If you have four ranges and each one corresponds to a different quarter of the fiscal year, select the first range, enter Q1, select the second range, enter Q2, and repeat the process for Q3 and Q4.
  • If you selected 2 under How many page fields do you want?, do the same as the previous example in the Field one box. Then, select two ranges, and enter the same name, such as H1 and H2, in the Field two box. Select the first range, enter H1, select the second range, enter H1, select the third range, enter H2, select the fourth range, and then enter H2.

  1. Click Next.
  1. On the Step 3 page of the wizard, select a location for the PivotTable report, and then click Finish.

Top of Page Top of Page

 
 
Applies to:
Excel 2007