The other day we were reviewing a model that had been prepared by a finance director. They had separate tabs for each period (week or maybe month) and had been clever enough to use the exact same structure for each. That made it much easier to create a front summary tab that was adding all the results together. For example, if revenue for the first period was say in cell B5 in the first tab, the summary tab included a formula like this =SUM(Period1!B5,Period2!B5,Period3!B5,Period4!B5,Period5!B5,Period6!B5,Period7!B5,Period8!B5,Period9!B5,Period10!B5,Period11!B5,Period12!B5).

A long sum formula that could be shortened

Using 3D referencing to create a summary tab

Where you’ve taken the trouble to create a lot of tabs with the same structure 3D referencing makes the final step (adding all the tabs together) much faster and easier. You don’t have to sit there clicking on all the individual cells in all the individual tabs creating a long complex Sum formula. A much quicker way of creating the Sum formula would have you following these steps (you can download the example if you click on the picture below):

  • Go into cell B5 in the ‘Summary’ tab and type “=Sum(“;
  • Click on the ‘Period1’ tab and click on cell B5 in that tab;
  • Press “Shift” and then click on the ‘Period12’ tab;
  • Press “Enter”.

A much shorter neater formula results, =SUM(Period1:Period12!B5), and it was faster to create too. We didn’t need to click on every single tab as we created the formula – just the first and last tabs.

Shorten a formula with 3d referencing

The beauty of 3D referencing

3D referencing makes it much faster to summarise up results across multiple tabs (where you’ve taken the trouble to use the same structure for each). But it also helps when you want to insert or delete an extra tab. If you insert a tab between tabs 1 and 12, e.g. for a new period, the 3D formula will automatically include the new results. You don’t have to modify the existing formula. If you delete a tab the formula will also automatically adjust without generating horrible #REF! errors as a tab disappears. Nice!

Read more

You’re reading stories from Financial Training Associates Ltd, the company that helps you master your Excel modelling universe.

Read more