Three-Dimensional References

A three-dimensional reference points at the same cell or range on multiple sheets — one formula instead of three cell adds.

Northgate Academy tracks Budget, Forecast, and Actual on separate tabs. Leadership wants English Q1 from all three in one formula.

James Okonkwo built =SUM(Budgets:Actual!B2) — one line instead of three cell adds. When a fourth sheet joined the group, the total updated automatically.

You will add Forecast and Actual sheets to NorthgateBudget.xlsx mirroring Budgets structure, then write 3D formulas.

Right-click the Budgets tab → Move or Copy. Tick Create a copy. Copy to Forecast. Repeat for Actual.

On Forecast, adjust a few figures (e.g. English Q1 13000 in B2). On Actual, type real spend (e.g. English Q1 12300 in B2).

Keep same layout — English stays B2 on every sheet. 3D refs require identical structure — same cells mean the same department and quarter on each sheet.

=SUM(Budgets:Actual!B2)

Excel sums Budgets!B2 + Forecast!B2 + Actual!B2 (if those three are consecutive tabs).

Order matters: Budgets must be the leftmost tab in the group; Actual the rightmost.

Sum every quarterly cell on every sheet in the group:

=SUM(Budgets:Actual!B2:E6)

That is 3 sheets × 20 cells = 60 values in one function.

On Summary, click B2. Type =SUM(Budgets:Actual!B2:E6). Press Enter.

Insert a new sheet between Budgets and Actual — it joins the 3D range automatically.

Budgets B2 = 12500. Forecast might show 13000. Actual 12300. =SUM(Budgets:Actual!B2) returns the sum of all three — one formula for the variance dashboard.

3D works with AVERAGE, MAX, MIN, COUNT:

=AVERAGE(Budgets:Actual!B2) — mean English Q1 across scenarios.

=MAX(Budgets:Actual!E2:E6) — highest Q4 figure on any sheet in the stack.

Combine with named ranges on each sheet if names are sheet-scoped consistently — 3D names are advanced; cell refs are clearer for learners.

If you rename or move Forecast so tabs are Budgets, Expenses, Actual, Budgets:Actual!B2 now includes Expenses!B2 — wrong data.

Keep scenario sheets adjacent. Or list sheets explicitly: =Budgets!B2+Forecast!B2+Actual!B2.

For NorthgateBudget with many departments, a PivotTable from stacked data scales better than huge 3D ranges. 3D refs suit small multi-sheet models with identical layouts.