For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

When to use named ranges in Excel models

NAM.B4 =Revenue * (1 - DiscountRate)
When to use named ranges in Excel models — Excel example preview

Replacing cryptic =$B$2*$G$14 references with human-readable Named Ranges so formulas document their own business logic.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

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 types
  • cal_GrossMargin — a calculated value
  • rng_Customers — a range
  • tbl_Sales — a Table
  • ctl_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+F each 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 — TaxRate and Tax_Rate both 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.

=PRACTICE

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

  1. Name only the ranges that represent important business concepts.
  2. Use formula auditing arrows to trace inputs and outputs.
  3. Check formulas row by row for inconsistent references.
  4. 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.