Named Ranges & Excel Tables

The Problem with Raw Cell References

=SUMIFS($C$2:$C$100, $B$2:$B$100, "Finance", $D$2:$D$100, ">="&$F$1)

Hard to read. Hard to maintain. If your data moves or the column order changes, every reference breaks.

Compare to:

=SUMIFS(Salary, Department, "Finance", Rating, ">="&MinRating)

This is what named ranges deliver — formulas that read like English.

Named Ranges

Creating a Named Range

Method 1 — Name Box (fastest)

1. Select the range (e.g., C2:C100)
2. Click the Name Box (left of the formula bar, shows cell address)
3. Type the name: Salary
4. Press Enter

Method 2 — Define Name dialog

1. Formulas → Define Name
2. Name: TaxRate
3. Scope: Workbook (available across all sheets) or specific sheet
4. Refers to: =Config!$B$2 (can reference another sheet)

Method 3 — Create from Selection

1. Select a range that includes header labels in the first row/column
2. Formulas → Create from Selection
3. Check: Top row, Left column, Bottom row, Right column
4. Excel creates named ranges using the labels as names

Example:
Headers in row 1: EmpID, Name, Department, Salary, Rating
Select A1:E100 → Create from Selection → Top row
→ Creates: EmpID=A2:A100, Name=B2:B100, Department=C2:C100, Salary=D2:D100, Rating=E2:E100

Rules for Named Range Names

• No spaces (use underscore: Tax_Rate)
• Cannot start with a number
• Cannot look like a cell reference (A1, R1C1)
• Case-insensitive (Salary = SALARY = salary)
• Max 255 characters

Using Named Ranges in Formulas

=SUM(Salary)                          → sum all salaries
=AVERAGE(Salary)                      → average salary
=SUMIF(Department, "Finance", Salary) → Finance total
=VLOOKUP(A2, EmpTable, 2, FALSE)      → reference a named table range

Named Constants

Named ranges don't have to be ranges — they can be constants:

Name: TaxRate
Refers to: =0.18   (not a cell, just a value)

=C2 * TaxRate    → "=C2 * 0.18" but readable and updatable in one place

Update TaxRate once → all formulas using it update instantly.

Named Formulas

Name: NetSalary
Refers to: =Salary - (Salary * TaxRate)

=NetSalary   → in any cell, shows the result

The Name Manager

Manage all named ranges in one place:

Formulas → Name Manager (or Ctrl + F3)

Functions:

• New — create a new name
• Edit — change name or reference
• Delete — remove a name
• Filter — show names scoped to current sheet vs workbook, names with errors, etc.

Finding and Fixing Broken Names

When you delete cells or move sheets, named ranges can point to #REF!:

1. Open Name Manager
2. Look for names where "Refers To" shows #REF!
3. Select → Delete (if unused) or Edit → fix the reference

Name Scope

Scope	Accessible from
Workbook	Any sheet in the workbook
Sheet	Only from that sheet

If two names are the same — one workbook-scoped, one sheet-scoped — the sheet-scoped name takes priority on that sheet.

Excel Tables

Excel Tables (called "ListObjects" in VBA) are the most important structural feature for data management. They transform a plain data range into a smart, auto-expanding, auto-filtering structure.

Creating an Excel Table

1. Click any cell in your data range
2. Insert → Table or Ctrl + T
3. Confirm the range and check "My table has headers"
4. Click OK

Alternatively: Home → Format as Table → choose a style.

What Tables Give You

Feature	Benefit
Auto-filter	Dropdown arrows on every header — always on
Auto-expand	Add a row below → the table grows; formulas auto-fill
Structured references	Formulas use column names instead of cell addresses
Banded rows	Automatic alternating colour for readability
Total Row	One-click sum/average/count at the bottom
Named table	Reference the table by name in formulas

Table Names

Every table gets a name (default: Table1, Table2...). Rename it:

1. Click inside the table
2. Table Design → Table Name (top-left of the Design tab)
3. Type: tblEmployees

Now reference it in formulas: =SUM(tblEmployees[Salary])

Structured References

This is what makes Tables powerful — formulas reference by column name, not cell address:

=SUM(tblEmployees[Salary])          → sum of the Salary column
=AVERAGE(tblEmployees[Rating])       → average rating

=SUMIF(tblEmployees[Department], "Finance", tblEmployees[Salary])
→ Finance salary total — completely self-documenting

=VLOOKUP(A2, tblEmployees, 3, FALSE)     → still uses col number (limitation)
=XLOOKUP(A2, tblEmployees[EmpID], tblEmployees[Name])  → much better!

Special Structured Reference Keywords

=tblEmployees[@Salary]              → current row's Salary (used inside the table)
=tblEmployees[#Headers]             → the header row
=tblEmployees[#Totals]              → the totals row
=tblEmployees[#All]                 → entire table including headers
=tblEmployees[[#Data],[Salary]]     → just the data (no header, no total)

Total Row

1. Click inside table → Table Design → Total Row
2. A "Total" row appears at the bottom
3. Click any total cell → dropdown appears: Sum, Average, Count, Max, Min, StdDev, etc.
4. Uses SUBTOTAL() internally so it respects filters — totals update when you filter

Adding and Removing Table Rows/Columns

• Add row: press Tab in the last cell of the last row — a new row is added
• Add column: type a header in the cell immediately to the right of the table
• Delete row: right-click row number → Delete → Table Rows
• Convert to range: Table Design → Convert to Range (removes table features but keeps formatting)

Slicers for Tables

Tables support slicers just like Pivot Tables:

1. Click inside table → Table Design → Insert Slicer
2. Select columns to create slicer buttons for

Dynamic Array Formulas with Tables

Tables + FILTER/SORT (Excel 365) = truly dynamic reports:

=SORT(FILTER(tblEmployees, tblEmployees[Department]="Finance"), 2, -1)
→ Filtered, sorted Finance employees — updates instantly when source table changes

Practical Examples

Example 1: Payroll Calculation with Names

Without names:

=IF(AND($C2>80000, $D2>=4), $C2*0.15, $C2*0.10)

With named constants and ranges:

Name: SeniorThreshold = 80000
Name: SeniorBonusRate = 0.15
Name: StandardBonusRate = 0.10
Name: MinSeniorRating = 4

=IF(AND([@Salary]>SeniorThreshold, [@Rating]>=MinSeniorRating),
    [@Salary]*SeniorBonusRate,
    [@Salary]*StandardBonusRate)

Update one named constant → all calculations update.

Example 2: Cross-Sheet Summary Using Table References

Sheet "Sales": tblSales with columns [Date], [Region], [Product], [Revenue]
Sheet "Summary":

=SUMIF(tblSales[Region], "North", tblSales[Revenue])
=AVERAGEIF(tblSales[Region], "South", tblSales[Revenue])
=COUNTIF(tblSales[Product], "SaaS")

Readable. If the Sales sheet adds rows, these formulas automatically include them.

Example 3: Dynamic Dashboard Source

Dashboard pivot table data source: tblSales
→ When new sales rows are added to tblSales
→ Right-click pivot → Refresh
→ Pivot automatically includes new rows (no need to update the source range)

Comparing Named Ranges vs Tables

Feature	Named Range	Excel Table
Auto-expand on new rows	No (must manually update)	Yes
Structured references	No	Yes
AutoFilter	No	Yes (always on)
Total row	No	Yes (one click)
Used in pivot table	Both work	Table auto-expands source
Multiple columns as one unit	No	Yes (table is one object)
Constants and formulas	Yes	No

Use named ranges for: constants, formula shortcuts, single-column references Use Tables for: all data, especially anything that will grow or be filtered

Common Mistakes

1. Named range doesn't expand with new data

Name: Salary = $C$2:$C$100
Add row 101 → still only sums up to C100
Fix: Convert data to a Table → use tblEmployees[Salary] (always includes all rows)

2. Table structured reference in a formula outside the table

[@Salary]  → works only INSIDE the table (current row context)
tblEmployees[@Salary]  → needed when formula is OUTSIDE the table

3. Table slowing down the workbook

Very large tables (100k+ rows) with many formulas can slow Excel. Consider:

• Using Power Query for large data
• Converting back to range once data is static

4. Sorting a table vs sorting a range

Sorting a Table: right-click a column header → Sort
→ Entire table row moves together
Sorting a plain range: risk of de-syncing columns if only one column is selected

Practice Exercises

1. Convert your employee data into an Excel Table named tblEmployees. Enable the Total Row showing sum and average salary.
2. Create named constants for TaxRate (0.18) and BonusThreshold (80000). Write a formula using these names.
3. Use =SUMIF(tblEmployees[Department], "Finance", tblEmployees[Salary]) to sum Finance salaries on a summary sheet.
4. Add a slicer to your Excel Table and filter by Department.
5. Use the Name Manager to find any broken named ranges (#REF!) and either fix or delete them.

Summary

In this chapter you learned:

• Named ranges: click Name Box → type name → Enter; or Formulas → Define Name
• Rules: no spaces, no cell-address-like names, max 255 chars, case-insensitive
• Named constants: =0.18 as a "Refers to" value — update once, used everywhere
• Name Manager (Ctrl+F3): view, edit, delete, filter all names; fix #REF! names
• Excel Tables (Ctrl+T): auto-filter, auto-expand, total row, banded rows, structured references
• Structured references: tblName[Column] for full column; [@Column] for current row
• Special keywords: [#Headers], [#Totals], [#All], [#Data]
• Tables as pivot sources → refresh auto-picks up new rows without changing source range
• Use tables for growing data; named ranges for constants and formula shortcuts

Next up: Conditional Formatting — automatically colour, highlight, and visualise data based on rules.