Named Ranges in Excel

A named range is a word you assign to a cell or range so formulas can refer to it by name instead of by cell address — for example =SUM(Sales) instead of =SUM($B$2:$B$100).

How do I create a named range?

The fastest method is the Name Box; use the Define Name dialog when you need to set a specific scope, or Create from Selection when you already have column headers to name from.

Method 1: Name Box

1. Select cells (e.g., B2:B100)
2. Click Name Box (left of formula bar)
3. Type name: SalesData
4. Press Enter

Now use =SUM(SalesData) instead of =SUM(B2:B100)

Method 2: Define Name Dialog

1. Select cells
2. Formulas tab → Define Name
3. Name: Quarterly_Sales
4. Refers to: =Sheet1!$B$2:$B$100
5. OK

Method 3: Create from Selection

When you have headers:
1. Select range including headers (A1:B100)
2. Formulas → Create from Selection
3. Check "Top row"
4. OK

Automatically creates names from column headers

How do named ranges make formulas easier to read?

A named range replaces a cryptic cell address with a word that describes what the data actually is, so the formula reads like a sentence.

Before (Hard to Read)

=SUMIF($B$2:$B$100, "North", $C$2:$C$100)

After (Clear and Readable)

=SUMIF(Region, "North", Sales)

Much easier to understand!

What do named ranges look like in a real model?

Financial models, sales dashboards, and dropdown lists all benefit the same way: the names carry the meaning so the formulas need less explanation.

Financial Model

Named Ranges:
Revenue = B2:B13
Expenses = C2:C13
TaxRate = B15

Formulas:
Gross Profit: =SUM(Revenue) - SUM(Expenses)
Net Profit: =Gross_Profit * (1 - TaxRate)

Sales Dashboard

Names:
Current_Year_Sales = Data!B2:B100
Prior_Year_Sales = Data!C2:C100
Sales_Target = Dashboard!B5

Formulas:
Growth: =(SUM(Current_Year_Sales) / SUM(Prior_Year_Sales)) - 1
% to Target: =SUM(Current_Year_Sales) / Sales_Target

Dropdown Lists

Create name: Departments
Refers to: ={"Sales", "Marketing", "Operations", "Finance"}

Data Validation:
Source: =Departments

Dynamic, reusable dropdown list

Can a named range grow automatically as I add data?

Yes — either wrap the reference in OFFSET so it recalculates its own size, or (the simpler, modern approach) convert the range to an Excel Table and reference it by table name.

Expand Automatically with Data

Traditional:
Sales = $B$2:$B$100  // Fixed range

Dynamic (using OFFSET):
Sales = OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)

Automatically includes new data added to column B

Using Tables (Best Practice)

1. Convert range to Table: Insert → Table
2. Table name: SalesData
3. Use in formulas: =SUM(SalesData[Sales])

Tables automatically expand with new rows

Where do I view or edit all my named ranges?

Formulas → Name Manager lists every named range in the workbook with its value, scope, and lets you edit or delete it in one place.

View All Names

Formulas → Name Manager

Shows all named ranges with:
- Name
- Value/Reference
- Scope (Workbook or Sheet)
- Can edit or delete

Paste Names in Formula

While editing formula:
- Press F3 (Paste Name dialog)
- Select name
- OK

Or just start typing and select from autocomplete

What are Excel's rules for naming a range?

Excel rejects any name that could be mistaken for a cell reference or that contains a space.

• Start with letter, underscore, or backslash
• Can't look like cell reference (e.g., A1, BC123)
• No spaces (use underscore: Sales_Data)
• Maximum 255 characters
• Case insensitive (Sales = SALES = sales)

Why bother with named ranges at all?

Beyond readability, named ranges cut down on the reference errors that come from typing or dragging cell addresses by hand.

• Readability: =SUM(Sales) vs =SUM($B$2:$B$100)
• Easy updates: Change range once, all formulas update
• Error reduction: Less chance of wrong cell references
• Documentation: Formula explains itself