How to Use Named Ranges in Excel

What is a named range?

A named range is a label you assign to a cell or range of cells. Instead of writing =SUM(B2:B100), you name B2:B100 "Revenue" and write =SUM(Revenue). Anyone reading the formula immediately understands what it does.

How to create a named range

Method 1 — Name Box (fastest)

Select the range → click the Name Box (top left of screen, shows the cell address) → type your name → press Enter.

Method 2 — Name Manager

Formulas → Name Manager → New. Gives you more control — you can add a comment, set the scope, and see all existing names.

Method 3 — Create from Selection

If your data has header labels, select the headers and data together → Formulas → Create from Selection → choose which row/column has the labels. Excel names each range automatically.

Naming rules

Must start with a letter or underscore
No spaces — use underscores: Monthly_Revenue
Can't be a cell address like A1 or B2
Case-insensitive: Revenue and REVENUE are the same name

Using named ranges in formulas

=SUM(Revenue)
=AVERAGE(Salaries)
=VLOOKUP(A2, EmployeeData, 3, 0)
=IF(TaxRate > 0.2, Revenue * TaxRate, 0)

💡 Dynamic named ranges with OFFSET

Create a named range that automatically expands as you add data: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1). This range always covers every non-empty cell in column A.

Practice what you just learned

ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.

Start practising free →

How to use named rangesin Excel