How to Use Multiple Data Sources in One PivotTable

A PivotTable summarizes large data tables.

A PivotTable is a feature in Microsoft Excel for compiling large amounts of spreadsheet data into summarized data. Using a PivotTable enables transposing data. Transposing is relocating headers that are placed at the top of the spreadsheet, and moving them down the left side column, or vice versa. Once headers are transposed and data is summarized, an analyst can see trends differently. Data from multiple sources, such as several spreadsheets, when combined, are analyzed in one PivotTable.

Things You'll Need

• Microsoft Excel
• Two or more spreadsheets to combine

Instructions

1. 

   Data with the same headers can be stacked in longer columns.

   Prepare the data to consolidate by making sure the source spreadsheets all have the same column headers and row names. This will enable you to place the correct data in the correct column during the consolidation (see Resources).

2. 

   The Pivot and Chart Wizard can expedite tasks.

   Click a blank cell in the Excel spreadsheet where you will create the consolidation.

   Hold down "Alt" + "D" + "P" to open the "PivotTable and PivotChart Wizard." If you plan to use this wizard often you can add it to the "QuickAccess" toolbar by clicking the tiny arrow at the very top of Excel to the right of the "Save" icon. Click "More Commands," then under "Choose Commands From," select "All Commands." Click "PivotTable and PivotChart Wizard," then click "Add," then click "OK."

3. Click "Multiple Consolidation Ranges" in the "PivotTable and PivotChart Wizard" then click "Next." On "Step 2a" click "I will create the page fields,'' then click ''Next." On ''Step 2b'' first open the first worksheet that contains the data. Click ''Collapse Dialogue,'' if it appears, for each cell range you select in the next step. Select the cell range, then click ''Expand Dialogue,'' then click ''Add." Where the wizard asks "How many page fields do you want?" click ''0,'' then click ''Next.''

4. In ''Step 3'' you will be asked to select a location for the PivotTable. Select a location for the first spreadsheet, then click ''Finish.''

5. Repeat this process for each cell range in each of the multiple spreadsheets, appending them by the column names which should be the same on each spreadsheet. When the last spreadsheet is consolidated you are ready to begin using the multiple data sources in the PivotTable.

Tips & Warnings

• Other data sources, such as external databases, can be consolidated. This requires more advanced knowledge of Excel and database connections.

• Do not choose any "Total" rows when selecting data to consolidate.

References

• Microsoft Office: Consolidate Multiple Worksheets

Resources

• O'Reilly Media: What is a Pivot Table