How to Use Named Ranges in Excel

Introduction

You inherit a spreadsheet full of formulas like =SUMIF(B4:B10, "East", C4:C10) and have no idea what B4:B10 or C4:C10 contain without scrolling up to check. Named ranges let you assign a label to a range, so the same formula becomes =SUMIF(Region, "East", Sales). The result is identical, but now anyone reading the spreadsheet can tell at a glance what the formula does.

The Dataset

Here's a quarterly sales table for a seven-person team:

	A	B	C
3	Rep	Region	Sales
4	Carter	East	18500
5	Nguyen	West	22300
6	Patel	East	15800
7	Torres	South	19600
8	Kim	West	24100
9	Brooks	South	17200
10	Adams	East	20400

The goal is to name each column so that every formula references Rep, Region, or Sales instead of raw cell addresses.

Creating a Named Range

The fastest way to name a range is through the Name Box, the small field to the left of the formula bar that normally displays the active cell address.

To name C4:C10 as "Sales":

Select C4:C10.
Click the Name Box (it will show "C4").
Type Sales and press Enter.

The range C4:C10 is now called Sales anywhere in the workbook. Repeat the same steps for the other two columns:

Range	Name
A4:A10	Rep
B4:B10	Region
C4:C10	Sales

You can also create names through Formulas > Define Name, which gives you additional options like scope and comments. For most situations, the Name Box is quicker.

Named Ranges in Formulas

Now you can use those names anywhere you'd normally type a cell reference.

Without named ranges:

=SUMIF(B4:B10, "East", C4:C10)

With named ranges:

=SUMIF(Region, "East", Sales)

Both return 54,700 (18500 + 15800 + 20400). The named version reads almost like a sentence: sum the Sales where the Region is "East."

That improvement carries across every function:

Goal	Without Names	With Names	Result
Count West reps	`=COUNTIF(B4:B10, "West")`	`=COUNTIF(Region, "West")`	2
Total sales	`=SUM(C4:C10)`	`=SUM(Sales)`	137,900
Average sales	`=AVERAGE(C4:C10)`	`=AVERAGE(Sales)`	19,700
Highest sale	`=MAX(C4:C10)`	`=MAX(Sales)`	24,100

Named ranges also simplify cross-sheet references. Without a name, pulling the sales total from another worksheet requires =SUM(Sheet1!C4:C10). With the name, it's just =SUM(Sales) regardless of which sheet you're on. Excel resolves the name to the correct location automatically.

Key takeaway: Named ranges don't change how a formula works. They change how easily someone can read it. If you share spreadsheets with others or revisit your own work months later, names save you from deciphering cell addresses.

Naming Rules

Excel is flexible with names but has a few constraints:

Rule	Example
Must start with a letter or underscore	`Sales` or `_Sales`, not `1stQ`
No spaces	Use `East_Sales` or `EastSales`
Cannot match a cell address	`Q1` is not allowed (Excel reads it as a cell), use `Q1_Sales`
Case-insensitive	`Sales` and `sales` refer to the same name

If Excel rejects a name in the Name Box without an obvious error message, it usually means the name conflicts with one of these rules.

Named Ranges in Data Validation

Named ranges pair well with drop-down lists. When setting up data validation, you can point the source to a name instead of a cell range.

If you've named the range B4:B10 as Region, the validation source becomes:

=Region

This is cleaner than typing =$B$4:$B$10, and if the underlying range changes, every drop-down that references the name updates along with it.

One catch: the Region range contains duplicates (East appears three times). A drop-down built from it would show three identical entries. If you need unique values, define a separate list of distinct regions somewhere in the workbook, name that range RegionList, and use it as the validation source instead.

When to Use Excel Tables Instead

If your data grows regularly, Excel Tables (Insert > Table, or Ctrl + T) solve a problem that named ranges don't. When you format data as a Table, Excel creates structured references that use the table and column name:

=SUMIF(SalesTable[Region], "East", SalesTable[Sales])

Tables have one major advantage over named ranges: they expand automatically. Adding a new row to a Table extends every formula and chart that references it. A named range defined as C4:C10 stays fixed at those seven cells unless you update it manually.

Feature	Named Range	Excel Table
Readable formulas	Yes	Yes
Auto-expands with new data	No (fixed range)	Yes
Works across sheets	Yes	Yes
Custom naming	Any name you choose	TableName[Column] format
Works in Data Validation	Yes	Limited

Named ranges work best for fixed ranges and single cells, like a tax rate stored in one cell that many formulas reference. Tables are the better choice when your data grows over time and formulas need to keep up.

Conclusion

Named ranges replace cryptic cell addresses with labels that make formulas self-documenting. Creating them takes a few seconds in the Name Box, and the payoff is formulas that are easier to read, audit, and hand off to someone else.

For conditional formulas that benefit from named ranges, see our guides on COUNTIF and SUMIF and SUMIFS. To build drop-down lists powered by named ranges, see our guide on creating drop-down lists. For more on how cell references behave when you copy formulas, see our guide on absolute and relative references.