Name Manager (Defined Names)
Not a function, but a piece of syntax. Gives a cell or range a plain name, so formulas can use that name instead of the raw reference.
A defined name is a drop-in replacement for whatever reference it points to, usable in any formula the same way MULTISHEET's SheetName! syntax stands in for a plain cell reference. Once TaxRate is defined to mean $D$2, a formula can simply say TaxRate and read like a sentence instead of a coordinate.
A name pointing at a single cell behaves as a plain scalar in arithmetic and comparisons; a name pointing at a multi-cell range behaves as that whole range anywhere a range is expected, inside SUM or AVERAGE, or as a VLOOKUP's table_array, exactly as if the raw reference had been typed in its place.
Syntax
| Argument | Description |
|---|---|
| Name | Required. A plain word (no spaces) that stands in for a cell or range, e.g. TaxRate, Sales, PriceTable. |
| Refers to | Required. The actual cell or range the name points to, e.g. $D$2 or $B$2:$B$6. |
A real example: a tax rate referenced by name across a whole workbook
With TaxRate defined to point at a single cell holding the rate:
Any formula anywhere in the workbook can use TaxRate the same way, and updating the rate once in the cell it points to updates every formula that references the name, with nothing to find and replace.
Worked examples
| A | B | D | |
|---|---|---|---|
| 1 | Item | Amount | Tax rate |
| 2 | Consulting | 500 | 0.08 |
(TaxRate is a defined name pointing at D2)
| A | B | |
|---|---|---|
| 1 | Month | Spend |
| 2 | Jan | 1200 |
| 3 | Feb | 900 |
| 4 | Mar | 1500 |
(Sales is a defined name pointing at B2:B4)
Common mistakes
- Adding a colon or cell letters after the name, as if it still needed pointing at. A name is used bare: Sales, never Sales:B4.
- Forgetting a name is case-insensitive but still needs to match exactly what's in Name Manager. A typo just becomes "unrecognized name" the way a typo'd cell reference becomes a broken formula.
- Assuming a named range loses its 2D shape inside a function like VLOOKUP. It doesn't; PriceTable still behaves as the full A2:B4 block, not a flattened list.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice named ranges →