Named Ranges and Structured References: Formulas That Say What They Mean

Here are two formulas that return the same number:

=SUMIFS($D$2:$D$11, $C$2:$C$11, "North")
=SUMIFS(Jobs[Hours], Jobs[Region], "North")

Result: 20 from both — the hours Amara Okafor logged on the three northern call-outs.

The first tells you where the numbers live. The second tells you what they are. That is the entire argument for naming things, and it is worth more than it sounds, because the person who has to check that formula in February is usually you, and by then $C$2:$C$11 is just an address in a building you no longer remember.

This guide is about the two systems Excel gives you for that: defined names, which you create deliberately, and structured references, which arrive free the moment a range becomes a Table. It covers how each one actually works underneath, where they help beyond formulas, how to make a name that grows with your data, the failure modes that make experienced people distrust names entirely — and the cases where the honest answer is to leave the range alone.

Tip: Every example below runs on the field service table shown after section 1. Copy it into a blank sheet starting at A1 and the cell references line up exactly. Structured references need Excel 2007 or later; XLOOKUP in section 6 and the # spill operator in section 7 need Excel 365 or 2021. Everything else works in any version still in use.

1) What a Name Actually Is

The mental model that causes the least trouble: a name is not a label stuck onto a range. It is an entry in the workbook's name table, and what it stores is a formula. Define a name called Hours over D2:D11 and what Excel writes down is:

Hours    refers to:    =Sheet1!$D$2:$D$11

That leading = is the whole reason names can do things a label could not. Because the stored thing is a formula, it does not have to be a range at all — it can be a number, a piece of text, or a calculation. Sections 4 and 7 both live off that fact.

There are three ways to create one, and they suit different moments:

The Name Box — select D2:D11, click the box to the left of the formula bar, type Hours, press Enter. Fastest for one name. The one thing to watch: you must press Enter. Clicking away leaves the name uncreated and you will not be told.

Formulas → Define Name — the long way, and the only way that lets you set scope and a comment at the moment of creation. Use it when the name matters.

Create from Selection (Ctrl+Shift+F3) — select A1:G11, tick Top row, and Excel makes seven names in one go, taking each name from the header above the column. It also quietly fixes what it must: a header of Travel km becomes Travel_km, because names cannot contain spaces. Convenient, and the single fastest way to end up with names you did not intend — it will happily create Job, Rate and five others whether or not you wanted them.

Once a name exists, =SUM(Hours) works anywhere in the workbook, and pressing F3 inside a formula pops up a list of every name you can paste.

2) Names Are Absolute by Default — and Why That Is a Mercy

When you create a name from a selection, Excel stores the reference absolute: =Sheet1!$D$2:$D$11, dollar signs included. Nothing about a name changes when you copy the formula that uses it.

🎯 Scenario: A summary block where the same three formulas get copied across four region columns, and none of them should drift.

=SUM(Hours)
=AVERAGE(Hours)
=COUNTA(Hours)

Result: 53, 5.3 and 10 — and they stay that way no matter which cell you paste them into. The equivalent =SUM($D$2:$D$11) behaves identically, of course; the difference is that you had to remember to type four dollar signs, and you only had to do it once for the name.

Excel does allow relative names — define a name while a specific cell is selected, strip the dollars in the Refers to box, and the name resolves differently depending on where it is used. It is a real technique and it is used in a handful of clever workbooks. It is also invisible: a formula reading =PriorRow*1.05 gives no hint that PriorRow means "one cell up from wherever I am". If you are not building the workbook you will maintain for the next three years, do not do this.

Pitfall: names are case-insensitive. Rate, rate and RATE are one name, not three. Excel will also silently re-case your existing formulas to match the last spelling you typed, which looks like corruption the first time you see it and is not.

3) Scope: Workbook, Sheet, and the Shadow

Every name has a scope — the region of the workbook in which it can be used unqualified.

Sheet scope exists for a good reason: twelve monthly sheets that each want a name called Total are much easier to build than Total_Jan through Total_Dec. Copy the sheet, and the sheet-scoped names copy with it, pointing at the new sheet's cells.

The trap is what happens when both exist. If a workbook has a workbook-scoped Rate and the sheet you are on has its own sheet-scoped Rate, the sheet-scoped one wins. Your formula reads =Hours*Rate on every sheet and returns a different rate on one of them, with nothing on screen to say so.

🎯 Scenario: Two people built the same summary on two sheets, one of them defined Rate locally, and now the March total is 4% out with no formula difference to point at.

Open the Name Manager (Ctrl+F3), sort by the Name column, and look for the same name appearing twice with different scopes. That takes about five seconds and is the first thing to check when a named formula returns the right shape of answer and the wrong number.

Tip: prefix sheet-scoped names when you can — Jan_Total scoped to Jan is redundant, and that redundancy is what makes the shadowing visible in the Name Manager list.

4) Named Constants and Named Formulas

A name does not need cells behind it. In Define Name, put a literal in the Refers to box:

VATRate        refers to:    =0.2
MileageRate    refers to:    =0.45
OvertimeAfter  refers to:    =8
CompanyName    refers to:    ="Northgate Field Services"

Now the workbook has a rate that lives nowhere and can be used everywhere:

🎯 Scenario: Bill each job at labour + parts + mileage, then add VAT — without a rates block sitting in the corner of a sheet that someone will sort or delete.

=(D2*E2 + F2 + G2*MileageRate) * (1 + VATRate)

Result for FS-2201: 686.28 — 408 of labour, 145 of parts, 18.90 of mileage, and 114.38 of VAT on top.

Note what did not happen: no helper cell, no $K$1 to protect, nothing to accidentally overwrite. Changing the mileage rate next April is one edit in the Name Manager and every formula in the workbook follows.

You can go further and store an entire calculation:

NetTotal    refers to:    =SUMPRODUCT(Jobs[Hours],Jobs[Rate]) + SUM(Jobs[Parts]) + SUM(Jobs[Travel km])*MileageRate

Result of =NetTotal in any cell: 5184.15 — labour of 3,556.50, parts of 1,440 and mileage of 187.65.

That is genuinely powerful and genuinely dangerous in the same breath. A named formula is invisible: the cell shows =NetTotal, and the only way to see what it does is to open the Name Manager. Used for one or two workbook-wide constants it is a gift. Used to hide a twelve-function calculation from the people who have to audit it, it is how a spreadsheet becomes somebody's private language.

Pitfall: there is one place a named constant will let you down — it cannot be seen by anyone reading a printout. If the VAT rate is a number an auditor is going to ask about, put it in a labelled cell and name that cell. VATRate refers to: =Rates!$B$2 gets you both.

5) Where Names Pay Off Outside a Formula

The under-sold half of defined names is everything that is not a formula, because several Excel dialogs accept a name where they will not accept a reference to another sheet.

Data validation. Put your region list somewhere out of the way, name it Regions, then set a dropdown's Source to =Regions. Before Excel 365 this was the only clean way to have a dropdown list stored on a different sheet — typing =Lists!$A$1:$A$4 into Source was rejected outright, and a name got round it. It is still the most maintainable way, because moving the list only requires updating one name.

Conditional formatting. The same restriction applied to formatting rules for years. =$D2>OvertimeAfter is also simply easier to read on the rule list than =$D2>8, and it makes the threshold editable in one place.

Chart series. Point a series at a name that resolves to a dynamic range and the chart grows with the data. This was the standard self-updating chart trick for a decade, and it still works.

Navigation. Press F5 or Ctrl+G, and every name in the workbook is in the list. Naming a print block Assumptions or InputBlock turns "which sheet was that on again" into two keystrokes. Print_Area and Print_Titles are themselves reserved names Excel maintains for you — you have been using named ranges without knowing it every time you set a print area.

6) Excel Tables: The Names You Get for Free

Select A1:G11 and press Ctrl+T. Rename it from Table1 to Jobs in the Table Design tab — that rename takes three seconds and is the difference between structured references reading like English and reading like nothing.

You now have names you never defined:

🎯 Scenario: Add a Labour column to the table itself, then total the whole thing without touching a single cell address.

Type in H1: Labour. In H2:

=[@Hours]*[@Rate]

Result: 408 on FS-2201 — and Excel fills the formula down all ten rows on its own, because that is what a calculated column does. Then anywhere on the sheet:

=SUM(Jobs[Labour])

Result: 3556.5

The two things that make this worth the Ctrl+T are both about time. First, the range grows. Type an eleventh job on row 12 and it joins the table; SUM(Jobs[Labour]) picks it up, the calculated column fills itself in, and every chart and PivotTable pointed at Jobs sees it. Compare with =SUM($H$2:$H$11), which does not, and does not complain either.

Second, the formula survives editing. Insert a column between Region and Hours and Jobs[Hours] still means hours. $D$2:$D$11 now means rate.

🎯 Scenario: The Rate column was typed by hand and one of them is wrong. Drive it from a rate card instead.

Put the four engineers and their rates in J2:K5 and name that block RateCard. Then in E2:

=VLOOKUP([@Engineer], RateCard, 2, FALSE)

Result: 68 for Amara Okafor, and a #N/A the moment somebody types an engineer who is not on the card — which is the point. A typed rate is silent when it is wrong; a lookup is loud.

Tip: the @ in [@Hours] is the intersection operator, and it only means "this row" from inside the table. Use Jobs[@Hours] from a cell outside the table and you get #VALUE! unless your row happens to align with the table's. From outside, aggregate — SUMIFS(Jobs[Hours], Jobs[Job], "FS-2203") — rather than reaching for a row.

Pitfall: structured references do not accept absolute anchoring the way $D$2 does. Dragging =[@Hours]*[@Rate] sideways gives you =[@Rate]*[@Parts], because the reference shifts by column just like a relative one. If you need a column pinned while dragging across, either write it as Jobs[[Hours]:[Hours]] or fall back to a plain reference for that one argument.

7) Dynamic Ranges: Three Generations of the Same Idea

A defined name over $D$2:$D$11 is fixed at ten rows. The old problem — how do you name a range that grows? — has three answers, and which one you use mostly dates your workbook.

OFFSET, the classic. In Refers to:

=OFFSET(Sheet1!$D$2, 0, 0, COUNTA(Sheet1!$D$2:$D$1000), 1)

Start at D2, move nowhere, take as many rows as there are non-empty cells, one column wide. It works, and it is what you will find in every workbook built between 2000 and 2015. The cost is that OFFSET is volatile: it recalculates on every single change anywhere in the workbook, whether or not anything it depends on moved. One of them is nothing. Forty of them, feeding forty chart series, is a workbook that takes two seconds to respond to a keystroke.

INDEX, the fix. Same behaviour, non-volatile:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D, COUNTA(Sheet1!$D$2:$D$1000)+1)

The colon between a fixed start and an INDEX result is the trick — INDEX returns a reference, not a value, so it can sit on the right of a range operator. Prefer this over OFFSET in anything new that still needs a dynamic name.

The # operator, the modern answer. If a spilled array is producing the list, point at the spill:

N2:  =UNIQUE(Jobs[Region])

Result: North, South, East, West spilling down N2:N5. Now =N2# means "whatever that formula spilled", however long it is today, and Regions refers to: =Sheet1!$N$2# gives you a self-maintaining dropdown source in one line with no COUNTA and no volatility.

And the fourth answer, which is usually the right one: make it a Table. Jobs[Hours] is already dynamic and cost you nothing.

Pitfall: COUNTA counts non-empty cells, not the last used row. One stray value in D400 makes your dynamic range 399 rows long, and a single blank in the middle of the data makes it stop short. The Table has neither problem, which is the strongest argument in this whole section.

8) The Name Manager, and Six Ways Names Rot

Ctrl+F3 opens the Name Manager. Two habits pay for themselves: sort by the Refers to column when hunting for problems, and use the filter dropdown, which has a Names with Errors option built in.

1. #REF! names. Delete column D and the name that pointed at it does not vanish — it becomes =Sheet1!#REF!. Every formula using it now returns #REF!, and the formula itself looks perfectly fine. This is the most common named-range failure by a wide margin, and the Name Manager's error filter finds all of them at once.

2. Phantom names from copied sheets. Copy a sheet into another workbook and its sheet-scoped names come too. Do it a few times and you get the dialog everyone has clicked Yes to a hundred times without reading: "A formula or sheet you want to move or copy contains the name X, which already exists." Each Yes leaves another dead name behind, usually pointing at the original workbook.

3. External links that will not die. Which is what those dead names are. A name reading ='C:\Users\...\[Budget v7.xlsx]Sheet1'!$B$2 is why Excel asks about updating links on a file that appears to have none. Data → Edit Links shows the link; the Name Manager shows the reason.

4. The shadow from section 3. A sheet-scoped name silently overriding the workbook-scoped one you meant.

5. Names created by add-ins. Some add-ins write hidden names, which the Name Manager does not display at all. If a workbook is mysteriously slow or bloated and the Name Manager looks clean, hidden names are a live suspect; only VBA or a third-party cleaner will list them.

6. Names nobody uses. The slow one. Fifty names accumulate over three years, eight are load-bearing, and nobody can tell which eight. Deleting a name that is still in use is instant and irreversible in the same click — every formula using it turns to #NAME?. Before a clean-up, Formulas → Use in Formula → Paste List dumps every name and its reference into cells, which gives you something to search the workbook against.

9) The Rules, and the Conventions Worth Adopting

Excel enforces these:

• No spaces. Travel km is rejected; Travel_km and TravelKm are fine.
• Must not look like a cell reference. Q1 is a real cell address, so it cannot be a name — which catches out everyone naming quarters. Q1_Sales or Qtr1 works.
• C, c, R and r are reserved on their own. They are row/column shorthand in the R1C1 world.
• Start with a letter, underscore or backslash, never a digit.
• 255 characters maximum, which is not an invitation.
• Case-insensitive, as covered in section 2.

And these are conventions, not rules — worth having because they make a name readable at a glance:

Say what it is, not where it is. Hours beats ColumnD. VATRate beats Rate2.

Prefix by kind if you have more than a dozen. rng for ranges, c for constants, tbl for tables — cVATRate, rngRegions. Ugly, and it sorts the Name Manager into something navigable.

Add the comment. The Define Name dialog has a Comment field almost nobody fills in. It shows up as a tooltip in the autocomplete list. "Standard rate, 20%, unchanged since Apr 2011" costs ten seconds and answers a question someone will otherwise have to ask you.

10) When Not to Name

Naming is not free, and a workbook can be over-named as easily as under-named. Three cases where a plain reference is the better answer:

One-off formulas. A name used once is an extra indirection with no payoff. =SUM(D2:D11) in a scratch calculation is clearer than =SUM(Hours), because D2:D11 can be verified by looking at the sheet and Hours cannot.

Anything a Table already gives you. If the data is a Table, Jobs[Hours] is already a name — a self-documenting, self-resizing one. Defining a second name over the same column is a synonym you now have to keep in sync.

When the name would lie. Sales pointing at D2:D11 was true in January. Rows got inserted, someone re-purposed the column, and now Sales refers to units. A slightly wrong address is a bug you find in ten seconds; a slightly wrong name is a bug that reads as correct for a year. Names are a promise, and the maintenance cost of that promise is real.

The honest heuristic: name it if it is used in more than one formula, needs to appear in a dialog that will not take a reference, or represents a business rule rather than a location. Otherwise leave it alone.

11) Nine Ways Names Go Wrong

1. Typing the name in the Name Box and clicking away — no Enter, no name, no warning.
2. A deleted column leaving a #REF! name — the formula looks fine and returns an error.
3. A sheet-scoped name shadowing a workbook one — right shape, wrong number.
4. Create from Selection over a whole block — seven names you did not ask for, one of which is Rate.
5. COUNTA dynamic ranges with a stray value below the data — a 400-row range and a chart with a long empty tail.
6. OFFSET in forty names — a volatile workbook that lags on every keystroke.
7. Dragging [@Hours] sideways — structured references shift by column, exactly like relative ones.
8. A named formula hiding a real calculation — the cell says =NetTotal and nothing else does.
9. Deleting a name in use — instant #NAME? everywhere, and no undo prompt worth the name.

Conclusion

Named ranges and structured references solve the same problem from two directions. Names are deliberate: you decide something deserves a word, and you pay a small maintenance cost forever after. Tables are automatic: press Ctrl+T, rename the table, and every column becomes a name that resizes itself and cannot drift out of date.

If you take one habit from this article, take the Table. It gives you readable formulas, a growing range and a header row that keeps meaning what it says, in exchange for one keystroke and one rename. Then define names for the handful of things a Table cannot express — the VAT rate, the overtime threshold, the list feeding a dropdown two sheets away — and open the Name Manager once a quarter to delete what has rotted.

The test for whether it worked is simple. Open the workbook in six months, click a cell in the summary block, and read the formula bar. If it tells you what the number means before you have to go and look at where it came from, the naming did its job.

Want to practise? Several exercises in the app are built on exactly these shapes — a lookup against a rate card, a conditional total across a column that later gains a row, and one where the range you need is the one that grew.