Read these two formulas and notice what your brain does:
=C2 * $E$1
=C2 * vat_rate
The first is a puzzle: what’s in E1? Scroll and find out. The second is a sentence. Same calculation, same speed, same everything — except one of them can be read, checked and trusted without leaving the cell. That’s a named range, and it’s the cheapest quality upgrade in Excel: no new functions, no new concepts, just the decision to call things by their names.
Naming a cell takes four seconds
Select the cell (or range), click into the Name Box — the
little white box left of the formula bar — type vat_rate,
Enter. Done. From now on, every formula in the workbook can say
vat_rate, and the Name Box’s dropdown
teleports you to it
from anywhere.
Names are workbook-wide, they can’t contain spaces (vat_rate,
not vat rate — the underscore habit), and they can’t collide
with cell addresses (Q1 is a cell; qtr1_total is a name).
Housekeeping lives in Formulas → Name Manager — the list of
every name, what it points at, and the comment field almost
nobody uses but should: one line saying what the number is and
who sets it.
Why this is more than cosmetics
Names are absolute by nature. vat_rate means the same cell
from every formula, everywhere — the whole
dollar-sign pinning question
simply doesn’t arise. Half of beginners’ $ mistakes are formulas
that wanted a name.
Names make wrongness visible. =C2*$E$2 — E2 instead of E1 —
looks completely plausible and is silently wrong.
=C2*vat_raet fails instantly with
#NAME?. Naming converts
the worst kind of error (quiet, plausible) into the best kind
(loud, immediate).
Names gather the knobs in one place. Every model has settings — rates, thresholds, targets, the growth assumption. Named and parked on one Inputs sheet, they become a control panel: anyone can see what drives the model and turn a knob without spelunking through formulas. This is the architecture the dashboard lesson calls the Calc layer, arriving early.
Where names stop and Tables start
A fair question at this stage of the path: with structured references naming every column automatically, what’s left for named ranges? A clean division:
- Tables name your data —
Orders[Amount], free, always current. - Named cells name your settings —
vat_rate,target_months,growth. Single values, deliberately chosen, documented in the Name Manager. - Named formulas (a name whose definition is a calculation) are the third act — that’s LAMBDA territory, and the naming instinct you’re building here is exactly what makes it land later.
The one abuse to avoid: don’t name everything. Forty names is a
second address system nobody maintains,
and stale names pointing at deleted cells produce lovely
confusing #REF!s. Name
the settings — the numbers a human might change — and let
Tables handle the data. A workbook usually wants five to fifteen
names, each earning its line in the Name Manager.
Here’s the test worth adopting: open any formula you wrote and
read it aloud. Every coordinate you have to go and look up is a
question your formula makes every future reader answer again.
=C2*vat_rate answers it once, for everyone, forever.
Four seconds per name. Sentences instead of puzzles. There is no cheaper quality you will ever buy in Excel.