Understanding about Pivoting Consolidated Data

Data consolidation refers to the collection and integration of data from multiple sources into a single destination. During this process, different data sources are put together or consolidated, into a single data store.

Because data comes from a broad range of sources, consolidation allows organizations to more easily present data, while also facilitating effective data analysis. Data consolidation techniques reduce inefficiencies, like data duplication, costs related to reliance on multiple databases and multiple data management points.

Pivoting Consolidated Data

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.

Page fields in data consolidations

Data consolidation can use page fields that contain items 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 that has one-page field and multiple items selected.

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

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, or you can use the Consolidate command (on the Data tab, in the Data Tools group).

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. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook.
3. Click on the PivotTable Table and PivotChard wizard icon on the Quick Access Toolbar.  
4. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
5. On Step 2a page of the wizard, click I will create the page fields, and then click Next. 
6. On Step 2b page of the wizard, do the following: 
   
7. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

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

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. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook. 
3. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
4. On Step 2a page of the wizard, click Create a single page field for me, and then click Next. 
5. On Step 2b page of the wizard, do the following: 
   
6. Click Next.
7. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

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

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, the 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. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook.
3. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
4. On Step 2a page of the wizard, click I will create the page fields, and then click Next. 
5. On Step 2b page of the wizard, do the following: 
   
6. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

The consolidate function in Excel allows an analyst to combine information from multiple workbooks into one place. The Excel consolidate function lets you select data from its various locations and creates a table to summarizes the information for you.