3D referencing

3D referencing is a cool trick in Excel.

Imagine you had a number of subsidiaries you wanted to consolidate up. Imagine you had thought ahead and had placed each subsidiary’s numbers on a separate tab using exactly the same struture for each. Now it’s time to insert a new tab at the front that adds together all the results from all the subsidiaries. Of course you could start creating a formula that looked a bit like this: “=’Sub1′!A1+’Sub2′!A1+’Sub3′!A1”. But with 3D referncing there’s a better way that will mean you don’t have to re-do all your formulas if Sub2 disappears.

What we’re talking about is kind of like the difference (when you’re adding up within one tab) between “=A1+A2+A3” and “=SUM(A1:A3)”. With the first formula your model will get screwed up with a #REF! error if you delete cell A2. The second formula adapts. 3D referencing is kind of like that but across tabs. 3D referencing adapts when you’re adding across tabs.

3D referencing

Start the Excel course

Really you need to see a quick example of 3D referencing at work in financial modelling, and that’s what we’ve got ready for you as part of our course on Excel formulas.