Consolidations with pivot tables - putting together a budget
The Multiple Consolidation Ranges feature allows you to pile a lot of similar worksheets on top of each other and summarise the results in a pivot table. David Carter offers some tips and gives a worked example - how to combine budgets from several departments into one for the whole company.
When you create a pivot table, the default data source in Step One of the Wizard is an Excel List or Database. However, another possible source is the Multiple Consolidation Ranges option. This is where the data you want to analyse is not simply a list of records with column headings, but looks like a formatted spreadsheet report with both row and column headings. A typical example would be a budget where you have both column headings (months) and row headings (account names).
When to use Multiple Consolidation Ranges
Putting a budget together is the most obvious use for Multiple Consolidation Ranges, when an accountant receives a number of budgets on spreadsheets from different departments and wants to consolidate them all into a single company budget.
There is a already a Consolidate facility in Excel's Data menu, but the great advantage of using multiple data ranges instead of Consolidate is that the source spreadsheets don't have to be in exactly the same format.
I've used also used this feature with timesheets. The client had several departments doing different types of work for various customers. Each department filled in a spreadsheet with the hours worked by each employee for each client during each day. At the end of the week we consolidated all the spreadsheets and worked out the total amounts to be recharged to each customer.
Another application is where you have two copies of the same report which have been printed at different times, and you want to identify any changes. You can use multiple data ranges to drop version A of the report on top of version B in a consolidation, then slice and dice to see where they differ.
But It's Not Intuitive
Multiple Consolidation Ranges, then, is a very handy feature for analysing a set of spreadsheet reports. But it is quite different in the way it works from a standard pivot table. In a standard pivot table, Excel simply displays a list of all the column headings and you can just drag and drop them where you want into the Row, Column, and Page fields - so it's very intuitive.
But with Multiple Consolidation Ranges you have to define your own Page fields yourself. That's a lot harder than just pointing and shooting at an on-screen list. With the Row and Column fields at least you don't have to invent your own, but you are only allowed one apiece and if you want more than one Row field in your final pivot table - which you normally do - you have to do some lateral thinking to get it in.
AW's Self-Teach Tutorial
The following notes (based on Excel 97) are aimed to help you get round some of the more awkward features in Multiple Consolidation Ranges. The examples I've given refer to the data in our self-teach tutorial on the subject - so it might be worth downloading that and having its sample Excel files on your screen when you read them.
The tutorial takes you through the task of combining three company P&L's into a single group consolidation. If you have never consolidated multiple data ranges before, it would be best to work through it now in order to master the basics.