Named ranges in Excel: how to create them and why they help

What a named range is

A defined name is a label that points to a cell (TaxRate → $B$2), a range (Revenue → $D$2:$D$13), a constant or even a formula. In a formula the name stands for whatever it points to: =SUM(Revenue), =C8*(1+TaxRate).

• A name refers to a fixed address, like a reference with $ signs, so copying a formula never moves it.
• Names are not case-sensitive: taxrate and TAXRATE are the same name.
• By default a name is valid in the whole workbook, so a formula on any sheet can use it without a sheet prefix.

How to create a named range

• Name box: select the cells, click into the Name box left of the formula bar, type the name and press Enter. This is the quickest way.
• Define Name: Formulas tab → Define Name lets you set the scope and add a comment.
• Create from Selection (Ctrl+Shift+F3 in Excel for Windows): select a table including its labels and Excel names each column or row after its header. Excel replaces spaces with underscores, so the label Tax rate becomes the name Tax_rate.
• Name Manager (Ctrl+F3) lists every name with its address, and is where you edit, fix or delete names.

Google Sheets has the same feature under Data → Named ranges.

Using names in formulas

Type the first letters of a name in a formula and pick it from the AutoComplete list, or press F3 to paste one. With the four names in column C of the example, the order formulas read like the shop's rules:

• Discount: =IF(B8="Yes",C8*MemberDiscount,0)
• Shipping: =IF(C8-D8>=FreeShipFrom,0,ShipFee)
• Total: =(C8-D8)*(1+TaxRate)+E8

Order O-303 shows why readable rules matter: a member with 160.00 of goods gets 16.00 off, which leaves 144.00, below the free-shipping threshold of 150. The customer pays 9.90 shipping and 165.42 in total. If the shipping fee changes, you change one cell and every order updates. Names also make lookups easier to read, for example =XLOOKUP(A2,Codes,Prices) instead of two locked ranges (see XLOOKUP vs VLOOKUP).

Worked example: pricing orders with four named constants

The yellow cells in B2:B5 are the shop's constants, and column C shows the name each one gets. Rows 8 to 13 price six orders. The grid uses cell addresses so it can be checked anywhere, and the formula list shows each formula with the names. Define the four names and both versions return exactly the same results.

Worked example: pricing orders with four named constants

	A	B	C	D	E	F
1	Constant	Value	Defined name
2	Tax rate	8%	TaxRate
3	Shipping fee	9.90	ShipFee
4	Free shipping from (after discount)	150.00	FreeShipFrom
5	Member discount	10%	MemberDiscount
6
7	Order	Member	Net value	Discount	Shipping	Total incl. tax
8	O-301	Yes	120.00	12.00	9.90	126.54
9	O-302	No	89.50	0.00	9.90	106.56
10	O-303	Yes	160.00	16.00	9.90	165.42
11	O-304	No	150.00	0.00	0.00	162.00
12	O-305	Yes	245.00	24.50	0.00	238.14
13	O-306	No	42.00	0.00	9.90	55.26
14
15	Total of all orders					853.92
16	Orders with free shipping					2
17	Member orders					3

Fictional shop and illustrative rates. Tax is charged on the goods after discount, shipping is shown without tax.

Why use named ranges (and when not to)

• Readable formulas: =C8*MemberDiscount explains itself, =C8*$B$5 needs a look-up.
• Fewer broken references: no forgotten $ sign when you copy a formula down or across.
• Navigation: type a name into the Name box and Excel jumps to it.
• Inputs in one place: rates, thresholds and scenario switches live in a labelled block instead of hidden inside formulas.
• Other features: a name works as the source of a data validation list or a chart series.

The downside is clutter. A workbook with hundreds of names nobody maintains, or names that point to deleted cells, is harder to audit than plain references. Name the inputs and key ranges a reader needs, not every cell.

Naming rules and scope

• A name must start with a letter, an underscore or a backslash. After that, letters, digits, periods and underscores are allowed.
• No spaces: write TaxRate or Tax_Rate.
• A name must not look like a cell reference: TAX2026 is a real cell (column TAX, row 2026), and C and R alone are reserved.
• Up to 255 characters, but short names are easier to read.

The scope decides where a name is valid. A workbook-level name is unique in the file. A worksheet-level name is valid only on its sheet, so Budget can exist once per sheet, and other sheets refer to it as Sheet2!Budget.

Names that grow: Excel Tables

A named range has a fixed size. Add a thirteenth month below Revenue and =SUM(Revenue) ignores it. Two fixes: convert the data into an Excel Table (Ctrl+T) and use its structured references, for example =SUM(Orders[Total]), which grow with the table automatically, or define a dynamic name such as =Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTA(Sheet1!$A:$A)) for a list with one header row, which always ends at the last filled row. The INDEX version is preferred over OFFSET because it is not volatile and does not recalculate on every change.

Common mistakes

• A misspelt name. =SUM(Revnue) returns #NAME?. Pick names from the AutoComplete list or with F3 instead of typing them.
• The name inside the quotes. =COUNTIF(Sales,">=SalesTarget") compares with the text "SalesTarget". Write ">="&SalesTarget.
• The header inside the range. Name the data only. A header in a criteria range is counted by COUNTIF and can be matched by a lookup.
• Names that look like cells. Q1, TAX2026 or R are rejected. Use Sales_Q1 or Tax2026Rate.
• Deleting the cells a name points to. The name turns into #REF! and so does every formula that uses it. Check the Name Manager after restructuring a sheet.
• Fixed-size names on growing data. A name for D2:D13 does not include row 14. Use an Excel Table or a dynamic INDEX range for lists that grow.