Excel® 2007 PivotTables and PivotCharts

Chapter 10. Building More Advanced PivotTables

Create a PivotTable from Multiple Consolidation Ranges

If your source data exists in two or more ranges, Excel can consolidate all the ranges and then produce a PivotTable report based on the consolidated data.

Many businesses create worksheets for a specific task and then distribute them to various departments. The most common example is budgeting. Accounting might create a generic “budget” template that each department or division in the company must fill out and return. Similarly, you often see worksheets distributed for inventory requirements, sales forecasting, survey data, experiment results, and more.

Creating these worksheets, distributing them, and filling them in are all straightforward operations. The tricky part, however, comes when the sheets are returned to the originating department, where all the new data must be combined into a summary report showing company‐wide totals. This task is called consolidating the data, and it is often difficult and time‐consuming, especially for large worksheets. However, Excel has a powerful PivotTable feature that can make it easy to consolidate the data and summarize it into a simple report.

Create a PivotTable from MultipleConsolidation Ranges

Note: This chapter uses the workbooks Division1.xlsx, Division2.xlsx, Division3.xlsx, and PivotTables2.xlsm, and the Invoices.accdb database, available at www.wiley.com/go/2007pivottablesvb, or you can createyour own sample database.

1. Open the workbooks ...