INDIRECT() in Excel: Why You Should Stop Using It
INDIRECT() is the most overused finance modelling function. Three specific problems and four alternatives by use case.
INDIRECT() is one of those small Excel modelling habits that causes outsized pain when models get inherited. It's convenient for building flexible references — =INDIRECT("'"&A1&"'!B5") — but it has three properties that should disqualify it from any model that anyone other than the original builder will use.
1. It's volatile
Every recalculation re-evaluates every INDIRECT in the workbook. On a 50-tab model with 200 INDIRECT calls, this is the difference between a 0.5s recalc and a 30s recalc. Volatile functions also trigger recalculation when totally unrelated cells change, which makes audits painful.
2. It breaks silently when sheets are renamed
No #REF! error. Just wrong numbers. The user has no idea anything went wrong — the formula still "works", it just points at nothing now and returns 0 or #REF! that's lost in a sea of other values.
3. It can't be traced
F2 doesn't show you what cell INDIRECT is pointing at. F5 → Special → Precedents doesn't follow it. You have to mentally evaluate the string concatenation to figure out where the reference goes. In a model audit, this is hours of additional work per page.
What to use instead
For cross-sheet references where the sheet is fixed:
Just use a normal reference. ='Lending'!B5. No INDIRECT needed.
For lookups across multiple sheets where the sheet name varies:
Use a single consolidation tab that pulls all sheets via fixed references, then INDEX/MATCH against the consolidation. One layer of indirection, fully traceable.
For "sometimes show this, sometimes show that":
CHOOSE or INDEX with a switch cell. Both traceable. Both non-volatile.
=CHOOSE(MATCH(scenario_cell, {"Base","Upside","Downside"}, 0),
Base_value, Upside_value, Downside_value)If you genuinely need a dynamic sheet reference (rare):
Build a helper column with the actual values and INDEX/MATCH against that. Avoid INDIRECT.
The general principle
In a model that other people will inherit, traceability beats convenience every time. Anything that breaks F5 → Special → Precedents is a future bug factory.
For our entire model catalogue, we have a hard rule: no INDIRECT, no INDIRECT.EXT, no OFFSET (similarly volatile and untraceable). Every reference is either direct or via INDEX/MATCH against a fixed range.
It takes 5% more upfront work and saves 50% of audit time. Always worth it.
Get the SFS Models Catalogue
The model that puts the principles in this post into practice. Bank-grade build, open formulas, no VBA. Same-day delivery.
SFS Models builds institutional-grade Excel financial models for banking and finance professionals. Browse the catalogue or get the free sample.