A named range is a plain-language name given to a cell or a range of cells. Excel’s official term is a defined name: a name that represents a cell, a range, a formula, or a constant value. Once the name Sales refers to B2:B5, you can write =SUM(Sales) instead of =SUM(B2:B5).
The point, per Microsoft’s documentation, is that names make formulas easier to understand and to maintain. The name reads like the business, and it is a single thing to update: repoint Sales once and every formula that uses it follows. This guide covers the three ways to create a name, the rules names must follow, what scope means, and the specific ways names break in long-lived workbooks.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total |
| 2 | Jan | 1,200 | 4,700 |
| 3 | Feb | 980 | |
| 4 | Mar | 1,340 | |
| 5 | Apr | 1,180 |
How do you create a named range in Excel?
Excel has three routes. They produce the same kind of name; they differ in speed and in how much you get to control.
The Name Box, for speed
Select the cell or range, type a name into the Name Box (the small box to the left of the formula bar), and press Enter. That’s the entire procedure. From then on the name works in formulas:
To use a name while typing a formula, type its first letter and pick it from the list Excel offers, or choose it from Formulas > Use in Formula.
Define Name, for control
The Name Box takes a name and nothing else. The dialog route takes everything: on the Formulas tab, in the Defined Names group, select Define Name. The New Name dialog accepts the name, an optional comment of up to 255 characters (a good place to record what the range is for), the reference itself in the Refers to box, and a Scope drop-down offering Workbook or the name of a worksheet. It’s the only creation route that lets you choose scope, which the next section unpacks.
Create from Selection, for labeled tables
When the data already has labels, Excel can turn them into names in one pass. Select the range including the row or column labels, then select Formulas > Create from Selection (or press Ctrl+Shift+F3) and check the box matching where the labels sit: Top row, Left column, Bottom row, or Right column.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 1,200 |
| 3 | Feb | 980 |
| 4 | Mar | 1,340 |
| 5 | Apr | 1,180 |
One detail worth knowing before you rely on it: per Microsoft’s page on the feature: “Named ranges that are created from selecting cells have a workbook-level scope.”
However a name was created, it lives afterwards in the Name Manager (Formulas tab, Defined Names group). It lists every defined name and table name in the workbook with its current value, its reference, and its scope. Double-click a name to edit what it is called or what it refers to; select it and choose Delete to remove it.
What does scope mean for a named range?
Every name has a scope: either a specific worksheet (Microsoft calls this the local worksheet level) or the entire workbook (the global workbook level). Scope is where the name is recognized without qualification, and a name must be unique within its scope.
Workbook is the default in the New Name dialog, and for most models it is the right choice: the name can be used in a formula on any sheet, and there is exactly one of it. A worksheet-scoped name works unqualified only on its own sheet; used from anywhere else it needs the sheet name in front, in the form Sheet1!Budget_FY08.
The useful side of worksheet scope is repetition. Because uniqueness applies within a scope, a workbook with twelve monthly sheets can carry a local name Total on each one without collision, and each sheet’s formulas mean their own month. Scope is chosen in the New Name dialog when the name is created; the Name Manager’s Scope column shows what every existing name got.
Why use a named range?
Microsoft’s documentation makes the case with a single pair: =SUM(C20:C30) against =SUM(FirstQuarterSales). A reference says where a formula points. A name can say what it means, and that matters most to the person reading the formula who did not write it. A reviewer can sanity-check =Price*Quantity at a glance; =B7*D12 sends them off tracing cells. Lookups gain the most, because they carry two or three ranges each; an INDEX and MATCH formula over Prices and SKUs explains itself in a way one over C2:C500 and A2:A500 never will.
Maintenance is the quieter payoff. When a source range moves or grows, you edit the name once in the Name Manager, and every formula that uses the name across the workbook picks up the change, without a find-and-replace across sheets.
A less known use: named ranges can drive data-validation dropdown lists. A dropdown whose Source is a named range like Departments is updated the same way, by editing the name’s Refers to range in the Name Manager; Microsoft documents this as the standard editing procedure for such lists. Every cell carrying the dropdown offers the new entries from then on.
How do named ranges break?
Two error values do most of the damage. Both have full write-ups in Excel’s error values, explained; here is the named-range angle on each.
A misspelled or deleted name gives #NAME?
The condition is plain: the formula refers to a name that is not defined. Two ways to get there with named ranges: the name is misspelled in the formula, or someone deleted the name from the Name Manager while formulas still used it.
The fix is to correct the name, not to hide the error. Microsoft’s instruction on this one is explicit: “Do not use any error-handling functions such as IFERROR to mask the error.” The #NAME? section of the errors guide covers the remaining causes, from typo’d function names to missing quotation marks.
A deleted target turns the name into #REF!
The #REF! error appears when a formula refers to a cell that is not valid, most often because the cells it referenced were deleted or pasted over. A defined name is a reference like any other, so it fails the same way: delete the rows or columns a name pointed at, and the name itself now holds the error. Open the Name Manager and its Refers to box shows something like this:
Excel anticipates this state well enough to ship a filter for it. The Name Manager’s filter list includes Names With Errors, which narrows the list to names whose current values contain errors such as #REF! or #NAME?. If you caught the deletion immediately, Ctrl+Z restores the cells and the name with them.
This is the named-range risk that matters in inherited workbooks. Names outlive the ranges they were built for, and nothing on the grid announces it: a broken name sits quietly in the Name Manager until a formula touches it, or until an auditor asks why a total changed. When a workbook you did not build lands on your desk, opening the Name Manager and applying the Names With Errors filter is a five-second check on what past edits left behind.
When is a named range not worth it?
Two honest caveats, both judgment calls rather than rules from Microsoft’s documentation.
Names earn their keep in workbooks that live for years and pass between people. In a throwaway sheet, where B2:B5 is visible on the same screen as the formula and nobody will ever maintain the file, naming every range is ceremony without payoff. The indirection also cuts both ways: =SUM(Sales) is clearer than =SUM(B2:B5) only while the name is accurate and well chosen, and a reader who needs to know exactly which cells feed a total still has to open the Name Manager to check. A workbook that has accumulated dozens of stale, broken, or near-duplicate names is harder to trust than one with none.
For a growing list, an Excel table can be the better tool. Microsoft’s dropdown guidance makes the case for tables directly: a dropdown based on a table picks up added and removed items on its own. A plain named range does not grow when new entries are typed below it; someone has to remember to widen it.
The short version
A named range is a defined name that refers to a range: create it in the Name Box for speed, in the New Name dialog when scope or a comment matters, or with Create from Selection when the labels already exist. Follow the naming rules (start with a letter, underscore, or backslash, no spaces, nothing that reads as a cell reference), and manage everything in the Name Manager, where one edit updates every formula that uses the name.
Use names where a workbook outlives its author and formulas will be read by people who did not write them. Skip them in disposable sheets. And treat the Name Manager as part of any review of an inherited file: broken names are invisible on the grid, and the Names With Errors filter finds them in seconds. When someone reworks the names in a model other people rely on, compare the old and new versions before trusting the new numbers, because repointing a name changes what formulas mean without changing how they look.
