Consolidate multiple data sources in a PivotTable

You can create a PivotTable by using data in different ranges. These ranges can be on the same sheet, on separate sheets, or even in separate workbooks. To do this, you must use the PivotTable Wizard and choose the Multiple consolidation ranges option. Although this wizard was removed from the user interface for Microsoft Excel for Mac 2011, you can still access it by using a keyboard shortcut.

1. To open the PivotTable Wizard, press COMMAND + ALT + P .

2. In step 1 of the wizard, click Multiple consolidation ranges, and then click Next.

3. In step 2a of the wizard, choose the number of page fields you want.

   By using page fields, you can group ranges of related data or you can have a page that shows a consolidation of all of the ranges. For example, if you're consolidating sales data from four different sheets, you might want to create a page field to consoldiate 2010 data from the different sheets, and another page field to consolidate 2011 data.

4. Now, follow the rest of the steps in the wizard.

See also

Create a PivotTable to analyze worksheet data