A pivot table is an interactive spreadsheet tool in Microsoft Excel that summarizes multiple data ranges in a single condensed, easy-to-read format for a simpler method of data reporting and analysis. Use it to arrange, count and total your data as you add different fields and create cross tabs. Some pivot table programs allow you to shuffle, hide or display certain categories to show several different relationships between the data.

Create a Pivot Table

In the Microsoft Excel spreadsheet workbook, click a blank cell that is not a part of the pivot table. Press "ALT+D+P" to begin the Pivot Chart Wizard. Select "Multiple Consolidation Ranges" and then click "Next." Select whether you will use a pivot table without page ranges, a pivot table with a single page field or a pivot table with multiple page fields depending on how many spreadsheets you want to consolidate. For a single page field, click "Create a Single Page Field For Me" on Step 2a of the Pivot Table Wizard and then click "Next." For multiple page fields, select "I Will Create the Page Fields" on Step 2a and then click "Next." Select the number of page fields you need in the option "How Many Page Fields Do You Want?" Select the cell range for each page field and enter a title for the range under the option "What Item Labels Do You Want Each Page Field to Use to Identify the Selected Data Range?" Select "Data/Pivot Table and PivotChart Report." Select "Multiple Consolidation Ranges" and then click "Next." Select the page option you need and then click "Next." Select each range you need and then click "Add." After choosing the amount of page fields, select a location for the pivot table and then click "Finish."

Consolidate Multiple Data Ranges

Add fields to the pivot table such as Row, Column and Value. Order each range of data that you want to analyze together in cross-tab format with matching names for Row and Column. Use the "Consolidate" command in the "Data Tools" group on the "Data" tab to consolidate the multiple data ranges. Multiple data ranges consolidated into a single pivot table make it easier to update the data and to add additional data.

Source:

Microsoft: Consolidate Data in Multiple Worksheets

Microsoft Office Online: Pivot Table Reports 101

Contextures: Pivot Table Tutorial - Multiple Consolidation Ranges