Sheet Formula Patterns for Multi-Tab Financial Models

This matters at scale. A standard bank syndicate DCF with 8 linked tabs (Assumptions, P&L, Balance Sheet, Cash Flow, FCFF, Returns, Sensitivity, Cover) can hit 50,000+ cells. At that size, two or three poorly chosen formulas compound into something your boss notices.

The 4 Sheet Formula Approaches at a Glance

Volatile means it recalculates on every change to any cell, anywhere in the workbook — including changes that have nothing to do with it. That distinction is where most performance problems start.

Direct References: Fast, Fragile

A direct cross-tab reference (='P&L'!C12) is non-volatile and resolves nearly instantaneously. For pulling a single cell — say, the EBITDA line into a Returns tab — it's the right call.

The fragility is real though. Rename "P&L" to "Income Statement" and every formula pointing at 'P&L'! breaks with #REF!. In a model where tabs get renamed during the quarterly board pack scramble, that's a genuine risk.

More practically, direct references don't compose well with dynamic logic. If you want to pull the same row from different tabs depending on a scenario toggle, you're stuck duplicating formulas. That's where INDIRECT gets tempting — and where the trade-offs sharpen.

INDIRECT: Powerful, Expensive

INDIRECT lets you construct a reference from a string, which means you can drive tab selection from an Assumptions cell:

=INDIRECT("'"&Assumptions!$B$2&"'!C"&MATCH("Revenue",'P&L'!$A:$A,0))

This survives tab renames (as long as you update the string in Assumptions) and makes scenario switching clean. One dropdown changes which tab feeds the entire model.

The cost: Google Sheets documentation explicitly classifies INDIRECT as a volatile function. It recalculates every time any cell in the workbook changes. In a 50,000-cell model, a handful of INDIRECT formulas can push recalculation time past 4 seconds per keypress. That's not a theoretical problem — it's the thing that makes an analyst open a second file and start copy-pasting, which is worse.

If you're going to use INDIRECT, localize it. One lookup table that resolves tab names into values, with every downstream formula pulling from that table via direct reference. The volatility hit stays contained.

Named Ranges: Underused, Underrated

Named ranges are non-volatile, survive tab renames, and make audit trails readable. =WACC_Base is clearer in a board pack formula than =Assumptions!$G$14, and when the CFO asks where that number comes from, you click Name Manager instead of hunting through 8 tabs.

The practical limit is maintenance. A mature FP&A model can accumulate 150-200 named ranges across assumptions, FCFF drivers, and scenario parameters. Google Sheets has no native way to document what each name represents, and stale names (pointing at cells that were repurposed) produce wrong answers without errors. Name them with a prefix convention (Assum_, Driver_, TV_) and document them in a dedicated Inputs tab.

For a DCF with a 14.2x EBITDA exit multiple as the terminal value anchor, the named range pattern looks like this:

// Named range: TV_EBITDAMultiple → Assumptions!$B$22
// Named range: EBITDA_Year5     → 'P&L'!$G$45

=TV_EBITDAMultiple * EBITDA_Year5

That's readable six months later. =Assumptions!$B$22 * 'P&L'!$G$45 isn't.

QUERY: For Multi-Row Pulls With Conditions

QUERY becomes useful when you need filtered aggregation across a tab — contribution margin by SKU, headcount by department, revenue by region. A direct reference can't do that without SUMIFS, which works but gets unwieldy on multi-condition pulls.

=QUERY('P&L'!A:G,
  "SELECT B, SUM(C) WHERE D='" & Assumptions!$B$3 & "' GROUP BY B",
  1)

This pulls department-level contribution margin for the period flagged in Assumptions, with a header row. The equivalent SUMIFS version would be 3-4 formulas and a helper column.

QUERY is non-volatile and 2-4x faster than an equivalent array of SUMIFS for large data ranges (as of May 2026 testing on datasets above 5,000 rows). The trade-off: QUERY syntax is SQL-adjacent but not SQL, and the error messages when it breaks are unhelpful. Build it in isolation, confirm output, then wire it in.

Note that QUERY doesn't work across files — for that you need IMPORTRANGE, which Google's documentation notes refreshes at most every 30 minutes and adds its own latency. In a live board pack, that lag can bite you.

Sheet Formula Volatility Tax: Why Your Model Is Slow

The performance problem in most large models isn't a single bad formula — it's a combination. A volatile INDIRECT driving 20 downstream SUMIFS, each referencing an entire column, in a 50,000-cell workbook, recalculating on every keystroke. The tax compounds.

The fix is tedious but effective: audit your volatile functions. In Google Sheets, there's no built-in volatile function tracker, so you're searching manually. The usual suspects are INDIRECT, OFFSET, NOW, TODAY, and RAND. Replace them where you can:

• OFFSET(A1,n,0) → INDEX(A:A,n+1) (INDEX is non-volatile)
• INDIRECT("'P&L'!A"&row) → resolve the lookup once in a helper cell, reference directly downstream
• Dynamic range bounds → calculate the bound in a named range cell, reference with A$1:A & BoundCell

This kind of refactor typically cuts recalculation time by 60-80% in models that have accumulated volatility over multiple quarters. The 4-second-per-keypress model becomes a half-second model. Worth the afternoon.

Where AI Fits Into This

The tedious part of cross-tab formula work isn't knowing which pattern to use — it's execution: wiring SUMIFS across 8 tabs with consistent column references, hunting volatile functions, reformatting QUERY output to match a board pack structure.

ModelMonkey handles that layer. You describe the pull in plain language ("sum revenue from P&L where period matches Assumptions B3, break out by region"), and it writes the formula targeting the right tab and column. It's an AI assistant embedded in the Google Sheets sidebar — faster than building by hand, and it won't drop INDIRECT where a direct reference would do.

As of May 2026, it works across both Google Sheets and Excel, which matters if your bank counterparty sends you a .xlsx and expects a formatted returns model back by Friday.

In summary: direct references for simple single-cell pulls where tab names are stable; named ranges for anything that needs an audit trail or reuse; QUERY for filtered multi-row aggregation; INDIRECT only when dynamic tab selection is genuinely required, and only localized. Volatile functions compound. Audit them before the model gets to 50,000 cells, not after.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.