What is a named range in Excel? Definition, rules, and when to use

Exceljet's definition is the short one: "A named range is one or more cells that have been given a name." What that buys is legibility. =SUMIFS(Amount, Region, "EMEA") tells a reader what it does; =SUMIFS($D$2:$D$5000, $B$2:$B$5000, "EMEA") tells them only where to go looking. On a model someone else will inherit, that difference is the whole argument.

Creating one

Excel offers three routes, all documented by Microsoft on the same page. The fastest is the Name Box, the small field to the left of the formula bar: "Select a cell. In the Name Box, type a name. Press Enter." For a block that already has headers, Formulas → Create from Selection reads the labels and names each row or column for you — tick whether the labels sit in the top row, left column, bottom row or right column, and "Excel names the cells based on the labels in the range you designated."

Everything after that lives in one place: "On the ribbon, go to Formulas → Name Manager. You can then create, edit, delete, and find all the names used in the workbook." It is also the only complete list of what has been named, including names pointing at ranges that no longer exist.

Google Sheets has no Name Manager. The equivalent is Data → Named ranges, which opens a side panel: select the cells, open the menu, type the name, click Done. Editing and deleting happen in the same panel, and Google's help warns about the consequence directly — when a named range is deleted, "any formulas that reference it will no longer work."

Using a name in a formula is the same everywhere: type the first letter and pick it from the suggestion list. Microsoft notes that names are not limited to plain ranges — "You can define a name for a cell range, function, constant, or table" — and that they "can also be used with dynamic array formulas and functions such as LET and LAMBDA."

The naming rules

The rules are stricter than most people discover before they hit them, and they differ slightly between the two applications.

Excel additionally refuses the single letters r, R, c and C, reserved by the R1C1 reference style. The convention that survives both rule sets: start with a letter, join words with underscores or capitals, and never use anything that could be read as a cell address.

Ablebits notes the property that makes names useful in the first place: by default "Excel creates a name with absolute references." Exceljet agrees — "named ranges are absolute references by default." A name behaves like $B$2:$B$40 without anyone typing dollar signs, so a formula built on names survives being copied anywhere on the sheet.

Scope: workbook-level and sheet-level names

Excel names carry a scope. A workbook-level name is, as Ablebits puts it, "recognized within the entire workbook, and you can refer to it simply by name from any sheet." A worksheet-level name is recognised only on its own sheet, and referring to it from elsewhere requires the sheet prefix.

This is the source of the confusing case where the same name resolves to different cells on different tabs — usually a workbook where Create from Selection was run on several sheets that share header labels. If a name gives you a number you cannot explain, the Name Manager's Scope column is where to look first. Google Sheets does not expose a scope setting: named ranges there are workbook-wide.

When a name earns its place — and when it does not

A name earns its place when the same range appears in many formulas, when the formula would otherwise be unreadable, or when the target is a constant someone will need to change later — a rate, a threshold, a reporting date. It also earns it for navigation: pick the name from the Name Box and the selection jumps there, which beats scrolling a fifty-tab model.

It stops earning its place in three situations. First, names for one-off ranges used in exactly one formula: you have added an indirection and gained nothing. Second, names that quietly go stale — a name defined over rows 2 to 500 does not grow when the data reaches row 900, and a formula silently reading the wrong number of rows is worse than one that breaks. Excel tables and structured references solve that properly, because a table expands with its data. Third, names that outlive their ranges: delete the underlying cells and Excel keeps the name, now pointing at #REF! — which is why a clear-out in the Name Manager belongs in any audit of an unfamiliar workbook.

How Formula Foundry handles this

Formula Foundry treats named ranges as a first-class part of a formula rather than as a string it does not understand. Named references get their own colour in the editor, alongside functions, ranges and constants, so a formula built out of names reads as structure instead of as a wall of text. Auto-suggest covers range names as well as function names: start typing and the names defined in the active workbook appear in the list — the fastest way to see what a workbook has actually been named without opening the Name Manager.

There is a real boundary worth knowing. Names from the workbook you have open are visible to the add-on; names living in a different, linked workbook arrive as opaque tokens, and Formula Foundry flags them as such rather than pretending to resolve them. The parser stays silent about unknown names for the same reason — it offers a "did you mean" against names it can see, but will not call a workbook name misspelled when it has no way to know the name exists.

For values rather than ranges, the add-on offers its own layer: global @@variables. You define a constant once in the sidebar — a tax rate, an FX rate, a cut-off — and write @@TaxRate inside a formula; the value is substituted when the formula goes into the cell, and changing it once updates everywhere it is used. This complements named ranges rather than replacing them: a variable is a scalar the add-on manages and your colleagues see through Formula Foundry, while a named range is a workbook object the spreadsheet manages and anyone opening the file can see. The features overview covers the editor, the variables panel and the snippet library together.

FAQ