When to use named ranges in Excel models

Replacing cryptic =$B$2*$G$14 references with human-readable Named Ranges so formulas document their own business logic.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.


Build a 3-Statement Financial Model [Free Course]
Wall Street Prep
Open on YouTube (opens in a new tab)
Named ranges can make a workbook dramatically easier to read, and they can also make it unmaintainable. The difference is not how many you have. It is whether each name corresponds to something the business has a word for.
=Revenue*TaxRate is better than =C14*Assumptions!$B$7 because a reader can
check it against their understanding of the business without leaving the formula.
=Helper_Range_7*Calc_Temp_3 is worse than cell references, because now you have
to look up two things instead of one, and neither of them means anything.
Where names clearly earn their place
Single-cell assumptions. Tax rate, discount rate, FX rate, start date, the salary on-cost percentage, the materiality threshold. These appear in many formulas, they change, and they have names people already use in conversation.
The model’s control cells. The selected period, the active scenario, the reporting currency. These get referenced from everywhere and are usually the first thing someone wants to change.
Lookup ranges used repeatedly. A rate card, a mapping table, a chart of
accounts. =VLOOKUP([@Code],AccountMap,2,FALSE) reads better than a range
address, and it only has to be fixed in one place if the table moves.
Print areas and ranges you navigate to. Print_Area is a built-in name, and
a name you define shows up in the Name Box dropdown, which is a fast way to jump
around a large model.
Where names cost more than they save
Anything inside a Table. Structured references already give you
tblSales[Amount], which is a name with better behaviour: it auto-expands, it is
scoped to its Table, and it cannot drift out of alignment with the data. Do not
define a second name on top of it. The division of labour is covered in
Excel Tables vs named ranges.
Intermediate calculation cells. If a value is used once, two rows below where it is computed, a cell reference is clearer. The name adds a lookup step and hides the proximity.
Every cell in a block. Naming forty cells on an assumptions sheet turns the Name Manager into something nobody will read, and an unreadable Name Manager is how broken names survive for years.
A reasonable ceiling for a mid-sized model is something like twenty to thirty names. Past that, ask whether a Table would do the job instead.
Get the scope right
Every name is either workbook-scoped (usable anywhere, must be unique across the file) or worksheet-scoped (usable on one sheet, can repeat on others).
Default to workbook scope for assumptions. A tax rate means one thing in the file, and a workbook-scoped name enforces that.
Use worksheet scope deliberately, for the case where several near-identical
sheets each need their own version of a name — one per region, one per entity.
Then North!Subtotal and South!Subtotal can coexist.
What you want to avoid is the accident: a workbook-scoped Rate and a
sheet-scoped Rate on the sheet you are working on. Formulas on that sheet will
use the local one, formulas elsewhere the global one, and the two will diverge
without any error ever appearing. Name Manager shows scope in its own column —
sort by it occasionally and look for duplicates.
Use a naming convention
Pick a prefix pattern and hold to it:
inp_TaxRate— an input somebody typescal_GrossMargin— a calculated valuerng_Customers— a rangetbl_Sales— a Tablectl_Period— a control cell
The prefix tells a reviewer, in the formula itself, whether they are looking at something that gets typed or something that gets computed. That is the same information the blue-font input convention carries visually, from your formatting system — here it travels into the formula bar too.
The hard rules Excel imposes: no spaces, cannot start with a number, cannot look
like a cell reference (TAX1 is fine, TX1 is not, and Q1 is a cell address
so it is rejected), 255 characters maximum, and names are case-insensitive —
taxrate and TaxRate are the same name.
The fastest way to create them
Select a block where the labels sit in the left column and the values to their right, then Formulas → Create from Selection → Left column. Excel names every value cell after its label, converting spaces to underscores, in one action. For an assumptions sheet that is the entire job done in two clicks.
For a single cell, click the Name Box (top-left, left of the formula bar), type the name, press Enter. Faster than the dialog.
The dynamic-name trap
You will find older advice recommending dynamic named ranges built from OFFSET
or INDIRECT:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
This works, and it was the right answer before Tables existed. It is now rarely the right answer, for two reasons.
OFFSET and INDIRECT are volatile: they recalculate on every change
anywhere in the workbook, whether or not anything they depend on moved. A handful
is fine; a few hundred will make a model feel broken. INDIRECT has the
additional problem that it is invisible to Trace Precedents and breaks
completely if the sheet it names is renamed.
Use a Table instead. It expands on its own, with no volatility and no string
parsing. If you genuinely need a dynamic name — for a chart series on data that
is not in a Table, say — build it from INDEX rather than OFFSET:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
INDEX is non-volatile in this position, which makes it the better of the two.
Audit names before sharing
Open Name Manager (Ctrl+F3) and work through the Value column:
#REF!in the value — the name points at deleted cells. It will produce errors on the recipient’s machine even if nothing on screen looks wrong now.- Names referring to another workbook — these become external links, with all the problems covered in the review checklist before sending.
- Names nobody uses.
Ctrl+Feach one across the workbook; if it appears only in the Name Manager, delete it. Copying sheets between files is the usual source, and it can multiply names into the hundreds. - Duplicate meanings —
TaxRateandTax_Rateboth defined, pointing at different cells. One of them is wrong and no error will tell you which.
Ctrl+F3 → filter → Names with Errors narrows the first category
immediately.
A useful last step: in a copy of the file, delete every name and see what breaks. Whatever still works did not need the name. This is destructive, so do it only on a copy — but it is the fastest way to find out which of your names are load bearing.
For the broader pass over formulas themselves, the formula audit checklist covers consistency, hardcoded values, and control totals.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Make formulas easier to inspect, explain, and fix when the workbook grows. For this article, the goal is to practice: Where named ranges earn their place in a model, where they cost more than they save, scope and naming conventions, the dynamic-name trap, and how to audit the Name Manager before sharing.
Practice workbook setup
Use a workbook with a few formulas, assumptions, and dependent outputs.
Practice workflow
- Name only the ranges that represent important business concepts.
- Use formula auditing arrows to trace inputs and outputs.
- Check formulas row by row for inconsistent references.
- Add notes for assumptions that affect multiple calculations.
Quality checks
- Names are descriptive and not too generic.
- Formula dependencies are visible and explainable.
- No formula relies on a hidden hard-coded value.
Common mistakes
- Creating too many names until the workbook is harder to understand.
- Leaving old names that point to deleted or moved ranges.
- Auditing only the final formula and not the intermediate logic.
Next actions
- Open Name Manager and remove unused or unclear names.
- Create a short assumptions table for model-critical inputs.
Formula focus: test INDIRECT(), OFFSET(), INDEX(), COUNTA() with normal rows, blank inputs, and at least one edge case.





