Named ranges assign meaningful names to cells or ranges. They make formulas more readable, easier to maintain, and reduce errors from incorrect cell references.

Creating Named Ranges

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

Using Named Ranges in Formulas

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!

Practical Examples

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

Dynamic Named Ranges

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

Managing Named Ranges

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

Naming Rules

• 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)

Benefits of Named Ranges

• 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