Established onExcel 365 and 2016 and later: names and table objectsGoogle Sheets, web: named ranges, no table objectsBoth: names survive deletion of their targets
A name attached to a cell or a range lets a formula say what it means rather than where it is. It is the oldest readability feature in these products and it has a specific weakness worth knowing before adopting it wholesale.
What a name buys
A formula that reads as hours times rate times one minus discount can be understood without the sheet open beside it. The reference survives insertion and deletion, because the name follows the cell. And the value has one home, so changing it is one edit.
Where the names live
Not on any sheet. They are stored in a workbook-level list, edited through a dialogue that most people who open a spreadsheet have never opened.
So a formula full of names is readable and unverifiable: a reader can see that it multiplies hours by rate and cannot see which cell rate is, without leaving the sheet and going to look. This is the same complaint as the entry on defined functions in the second part, and it is the reason names are not an unqualified good.
The scope trap
A name can belong to the whole workbook or to one sheet. Two names spelled identically can therefore exist, one of each kind, and a formula referring to that spelling resolves to whichever is in scope where it sits.
Copying a sheet duplicates its sheet-scoped names onto the copy, which is how a workbook acquires a dozen names with the same spelling and different targets. Nothing about the formulas indicates which one any of them uses.
Names that outlive their targets
Deleting the cells a name points at does not delete the name; it leaves it pointing at an invalid reference. Those persist in the list indefinitely and propagate into every workbook the sheet is copied into.
Any inherited file of a certain age has several, and clearing them out is a five-minute job that nobody does because the dialogue is not somewhere anybody goes.
The better answer for data
Structured references into a table object, which the entry on one table to a sheet describes. They read like names, they appear in the formula where they can be seen, they extend automatically when rows are appended, and they are not stored anywhere invisible.
The important limitation is that this is an Excel feature. Google Sheets has named ranges and no table objects, so a workbook using structured references does not convert, and a Sheets user reaching for the same benefit has named ranges and their weaknesses.
What to name and what not to
Name the things that are conceptually single: a rate, a threshold, a reporting date, a tax band. Those are exactly the values that would otherwise be typed inside formulas, which the next entry is about.
Do not name every range in a workbook. A hundred names is a second namespace that nobody maintains, and the readability gain disappears once the reader cannot remember which names exist.
Auditing the list on a file you inherit
Open the name manager and read it. Expect to find names pointing at invalid references, names duplicated per sheet, names created accidentally by importing, and names whose purpose nobody can state.
Delete the broken ones, and put the surviving ones on a sheet: a small table listing each name, what it points at and what it is for. That is the documentation the feature does not provide, and it takes ten minutes once.
The accidental names
Both products create names automatically in some circumstances, including when data is imported and when a table is created. These accumulate, they are not obviously distinguishable from deliberate ones, and they are the main reason an inherited workbook has forty names when somebody meant to create four.
What we cannot verify
Name scoping, the persistence of invalid names, and the absence of table objects in Google Sheets are all documented by the vendors and were confirmed on current builds. The claim about automatic name creation is observable and the exact circumstances differ by version and by import route; we have not enumerated them.
The name that is not a name
A cell reference typed into the name box moves the cursor. Typing a word there creates a name for the current selection, immediately, with no confirmation.
That is the fastest way to create a name deliberately and a common way to create one accidentally, and it is why inherited workbooks contain names that are single letters and misspellings of column headings.
What a name does not survive
Copying a range of cells into another workbook carries the formulas and not the names, so the copies arrive pointing at names that do not exist there. The result is a visible error rather than a wrong number, which is the fortunate outcome, and it is still a surprise on a Friday.
In short
- A name lets a formula say what it means rather than where it is.
- The names live in a dialogue rather than on a sheet, so a formula is readable and unverifiable.
- Two names with the same spelling can exist at workbook and sheet scope.
- Deleting the target leaves the name pointing at an invalid reference, indefinitely.
- Structured references are the better answer and exist in only one of the two products.
- Name conceptually single values, not every range, and audit the list on inherited files.