Trick for aggregating in budgets and forecasts

When you build budgets/ forecasts, you often need to be able to aggregate a number of sheets or else do some consolidation of sheets. The common way is just to add them up one by one. In the image below we want to get a total for Revenue of the 3 areas. The formula would be something like

=Africa!C5+Europe!C5+Asia!C5

aggregation

This will work fine, but there is another way which gives you a major benefit in that you can include/ exclude a sheet simply by moving the sheet out of an area.

In order for this to work however, it is CRITICAL that you have applied the concept of consistency across the sheets. You will see why shortly

The steps to follow are:

  1. In the summary sheet, click in the cell which will be doing the summing (C5 in the image below)
  2. Type =Sum(
  3. Click on the first sheet to include (in this case the Africa sheet) and click on the cell (cell C5)
  4. Now, and ONLY now, hold your SHIFT key down
  5. Click on the last sheet you want to include (Asia in this case)
  6. Close the bracket and click enter.

aggregation

As shown above the answer will be the same but look at the resultant formula of

=SUM(Africa:Asia!C5).

Note that it only mentions the cell to use (C5) once. This is why the consistency is so important. It will look at ALL the sheets from Africa to Asia (in between these two is the Europe sheet) and add up anything in cell C5 in all those sheets.

If we move the Europe sheet outside this area (say to the right of the Asia sheet) it will not be included in the sum. If you move it back into the area between Africa and Asia it will be included.

Some other notes on this feature:

  • You can only move the sheets between the 2 identified sheets in and out. If you wanted to remove Africa in the above example you couldn’t as Excel would follow the sheet. To avoid this problem you can insert two blank sheets called Beginning and End and use these as the reference points.
  • You can use the same technique with the AVERAGE function

The video clip below shows some other useful aspects and benefits of this

To follow along go to 6.1

Back to: Budgeting and Forecasting with Excel Course > Aggregation