A named range is a label you attach to a cell or a block of cells so you can write =SUM(Expenses) instead of =SUM(Sheet2!$B$2:$B$500). Create one by selecting the cells, clicking in the Name Box to the left of the formula bar, typing a name with no spaces, and pressing Enter. From then on that name works in any formula on the workbook and always points at those cells, even if you scroll away or move to another sheet.
Named ranges pay off most on converted data. When you drop a master list next to your extracted rows, an account code map, a vendor lookup, a category table, naming it once means every VLOOKUP, XLOOKUP, SUMIF, and dropdown points at CategoryTable rather than a hard coded $A$2:$B$200 that breaks the moment someone inserts a row. The formula reads like a sentence and the reference stops being fragile.
What is a named range in Excel?
It is a name that stands in for a reference. Most named ranges point at a group of cells, but a name can also hold a single cell, a constant like a tax rate, or even a formula. Wherever Excel expects a reference or a value, you can use the name instead, and the name resolves to whatever it was defined as.
The payoff is threefold: formulas become readable, references become absolute automatically so they do not drift when copied, and you update one definition instead of hunting through dozens of formulas. A rate stored as a named range called VAT is changed in one place and every formula using it follows.
How do I create a named range?
There are two routes, and which you pick depends on how much control you want.
| Method | Steps | Best for |
|---|---|---|
| Name Box | Select the cells, click the Name Box, type a name, press Enter | Fast, everyday naming |
| Define Name | Formulas tab, Define Name, fill in name, scope, and range | Setting scope or a comment |
| From Selection | Select data with headers, Formulas tab, Create from Selection, tick Top row | Naming many columns by their headers at once |
Create from Selection is the hidden time saver. Select a table that has a header row, run it, and Excel names each column after its header automatically, so a column headed Amount becomes a range called Amount in one action. The Name Manager, opened with Ctrl+F3 or from the Formulas tab, is where you review, edit, and delete everything you have named.
What are the rules for naming a range?
Excel is strict about names, and breaking a rule gives you a vague error rather than a clear one, so it is worth knowing them up front.
| Rule | Detail |
|---|---|
| No spaces | Use an underscore instead: Total_Sales, not Total Sales |
| Start with a letter or underscore | A name cannot begin with a number |
| Cannot look like a cell address | Q1 or TAX2026 can clash with real cell references and is rejected |
| Must be unique in its scope | Two workbook level names cannot match |
| Up to 255 characters | Though short and descriptive beats long every time |
The cell address rule catches people who name a range after a quarter or a code. Q1 is a valid cell reference, so Excel will not let you use it as a name. Add a word to break the clash, like Q1_Sales, and it is accepted. Case does not matter for matching, so Sales and sales are treated as the same name.
What is the scope of a named range?
Scope decides where the name is visible. A workbook scoped name works in every sheet. A worksheet scoped name works only on the sheet it was created on, which lets you reuse the same name, say Total, independently on several sheets.
| Scope | Visible from | Use when |
|---|---|---|
| Workbook | Any sheet in the file | A shared lookup table or a single global rate |
| Worksheet | Only its own sheet | Per-sheet names you want to repeat, like monthly tabs |
The Name Box always creates workbook scope, which is fine for most work. To set worksheet scope you have to use Define Name and pick the sheet from the Scope dropdown. One catch worth remembering: you cannot change a name's scope after the fact. There is no dropdown to flip it in Name Manager, so if you need a different scope you delete the name and recreate it.
How do I make a named range that grows automatically?
A plain named range is fixed to the cells you selected, so rows added underneath are left out. Two ways solve that. The clean one is to turn the data into a table with Ctrl+T and use its structured references, because a table expands on its own and its column names behave like living named ranges. The formula approach uses OFFSET and COUNTA:
=OFFSET(Data!$A$2, 0, 0, COUNTA(Data!$A:$A)-1, 1)
COUNTA counts how many entries exist and OFFSET returns a range that tall, so the name resizes as you add rows. The tradeoff is that OFFSET is volatile, meaning it recalculates on every change anywhere in the workbook. On one list you will never feel it, but across a big model it adds drag, which is why a table is the better default now.
How do I edit or delete a named range?
Open Name Manager with Ctrl+F3. Select a name to change what it refers to in the Refers to box at the bottom, or click Delete to remove it. Deleting a name that formulas still use turns those formulas into #NAME? errors, so before you delete, check nothing depends on it. A quick way to audit usage is to look for the name in your formulas with Find, or to note that a broken name shows up as #NAME? the moment it goes.
If a name points at the wrong cells after you moved data around, you do not delete and recreate it. Just edit the Refers to reference in Name Manager and every formula using the name updates at once. That single point of change is the whole reason named ranges are worth the setup.
Using named ranges on data converted from a PDF
Named ranges shine right where converted data is most fragile: the lookup. Convert the document into clean rows with our PDF to Excel converter, paste your master list, such as a code-to-name map, onto a spare sheet, and name it CodeMap. Now your lookup reads =VLOOKUP(A2, CodeMap, 2, FALSE) instead of a raw address, and it keeps working when you add codes to the map because you defined the name to cover the growing table. When you build a page of totals, a named Amount column makes every SUMIF and SUMPRODUCT self documenting.
This is the natural companion to the lookups themselves. A named range is what you feed a VLOOKUP after a conversion so the reference survives edits, and it is what a dependent dropdown list uses under the hood to switch its choices. If you are pulling master data out of many documents to build those lookup tables, software that reads documents and pulls the fields at scale gives you a clean, consistent table to name and reference, rather than a list you retype each month.
The short version
Select the cells, type a name with no spaces in the Name Box, and press Enter. Use Create from Selection to name a whole table by its headers at once. Keep names off anything that looks like a cell address, choose workbook scope unless you deliberately want per-sheet names, and remember scope cannot be changed after creation. For lists that grow, name a table instead of a fixed range. Then point your lookups and totals at the names so a converted sheet stays readable and does not break when rows move.
Last updated July 2026.