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.
NOTES AND TIPS
Format of the source data
If you are combining several spreadsheets, they may be in different workbooks. The first and obvious thing to do is to copy them all into the one Excel workbook as separate worksheets, so you keep all the work within a single workbook.
Don't have any totals in your source data. If you do, the pivot table will add them up and you will get double counting. The source data should be just that - data only. If you have to have totals in your source, make sure that they are at the edges of the spreadsheet so you can avoid them when highlighting the data range.
Remember that the multiple consolidation ranges feature in Excel can only handle ONE row field and ONE column field. When you highlight the data ranges to be consolidated, you should highlight only the one row and the one column that are adjacent to the data.
Creating Page Fields
In step 2a of the Wizard, the default is for Excel to create a page field for you. However, the resultant fields are just labelled Item 1, Item2 etc which isn't very meaningful. So always change the default to I Will Create the Page fields.
You can make up to four page fields. When Excel ungreys the boxes you have to click onto Field One, Field Two etc and type in the Page name(s) you want to give to the range that is highlighted in the big All ranges box.
As to what the Page names should be, it all depends on your data. But, taking the example of the tutorial where three companies are combined into one group total, for Field One I would highlight Company A in the All ranges box, then type CpyA into the Field One box, then highlight Company B in the All ranges box, then type CpyB into the Field One box, and so on.
Taking this further, suppose you were putting both budget and actuals into this consolidation, and you had one worksheet for Company A Actuals, and one worksheet for Company A Budget. This would merit a second page field. So with Company A Actuals highlighted in the All ranges box, you would type Cpy A into Field One, and Actuals into Field Two. Then with Company A Budget highlighted in the All ranges box, you would now type Cpy A into Field One again, but now Budget into Field Two.
You have to suck it and see a bit with Page fields. A couple of tips: I often rename source worksheets to try and make their worksheet names similar to their Page names. So when entering Page names into the Field boxes I just have to copy them from what I see at the bottom of the worksheet - no thinking required.
A second tip is, after you have entered the Page Field names, make a point of checking that Excel has stored them all correctly. Sometimes they seem to drop off. Click onto each range in the All ranges box and make sure that values come up in the Field boxes each time.
Change Count to Sum
When the pivot table is generated, you will often find that Excel has decided to Count the data instead of Summing it, and the resultant pivot table just shows lots of 1s and 2s.
I think this is because at least one field in the data range was non-numeric so Excel adopts Count as a default. At any rate, it is no problem when you realise what has happened. Simply right click on the data to pull up the pivot table menu, then in Field or Field settings, change Count to Sum.
Type in your own row, column & page names
When the pivot table is generated, the headings are simply Row, Column, Page1, Page2 etc. You can rename these with something meaningful. For example, double left click on the grey Row field button in A4 or A5. This brings up the Pivot Table field box. At the top of the box the word Row is highlighted. Simply overtype it with a sensible description.
How to create multiple Row fields - 1
Typically, your budget worksheets may have the Account Number in the first column, Account Name in the second. - something like 51010 Sales of Hardware, 51020 Sales of Software, etc etc.
You will find that if you highlight both account number and account name in your data range, the consolidation won't work - Excel can only handle one row field, not two.
To get both account number and account name into the consolidation, you need to combine them into one field via Excel's Concatenate command. Suppose that Account Number is in column A of your spreadsheet, Account Name is in column B, and the budget figures are in columns C onward - Jan, Feb, Mar etc.
Insert a blank column to the right of Account Name. If 51010 is in A2, and Sales of Hardware is in B2, in C2 type =A2&"-"&B2 This should give the result 51010-Sales of Hardware in C2, so you have account number and name but combined into a single field.
When you are highlighting the data ranges to create the pivot table, leave out columns A and B and just highlight this new column C.
If you have trouble Concatenating, there is a Wizard available via the Functions-Text menu option.
Concatenate all worksheets simultaneously
You will have to concatenate the data on all your worksheets. However, if you group the worksheets, you can do all of them in one go. To group several worksheets, go down to the worksheet tabs at the bottom left of the screen. Click into the far left one. Now hold down your Shift key. Now click onto the far right one. All the worksheets should now be highlighted, showing they are grouped. If you now do your concatenate operation, you will find that all the other highlighted worksheets are concatenated too. Neat, eh?
If you want to ungroup your worksheets, right click on any worksheet tab and select Ungroup Sheets at the top.
How to Create Multiple Row fields - 2
OK. You have got both account number and account name into your pivot table by combining them into a single field.
But you need a third Row field, that is, the summary categories such as Sales, Cost of Sales, Admin Expenses, Financial Expenses etc.
Create this manually by using the Group command. Suppose that the first sales account 51010-Sales of Hardware is in A3 and then the last one is in A10. Simply highlight rows A3 to A10 with the mouse. Then right click for the pivot table menu - Group and Outline - Group.
Excel creates a second row field, Row2, with Group1 displayed at the top. Click onto Group1 and just type over it with SALES or whatever description you want.
Now go ahead and create the rest of your account categories such as COST OF SALES etc.
Once you've created these categories, you can summarise the pivot table down to category level by using the Group - Hide Detail/Show Detail command. Rather than go through the menu, use the shortcuts for these. So, to hide the detail for SALES, for example, simply double click on SALES. To expand it and show the detail, just double click on it again.
Grouping Months into Quarters
Personally, I like to do the same thing with the columns as well as the rows. Rather than show the 12 months individually I group them into four quarters. Again, to do this, simply highlight the column headings Jan, Feb, Mar then right click for menu - Group and Outline - Group. Then over Group1 simply type Qtr1 or whatever.
If you include the wrong items in a group, you can ungroup them all and start again by the same process. In the Group and Detail menu simply use Ungroup instead of Group.