How to Use Named Ranges in Excel
Named ranges make formulas easier to read and maintain. Learn how to create them, use them in formulas, and avoid common workbook mistakes.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
Named ranges let you give a cell or range a readable name, then use that name in formulas. Instead of referencing H2:H13, you can reference MonthlyRevenue. Instead of B3, you can reference TaxRate.
That makes formulas easier to read, especially in budgets, invoice templates, dashboards, and recurring finance models where the same inputs are used across several sheets.
The syntax
Named ranges do not have their own formula syntax. You create the name first, then use it like a normal reference:
=SUM(MonthlyRevenue)=Subtotal*TaxRateThe name points Excel to the underlying cell or range.
How to create a named range
Step 1. Select the cell or range you want to name.
Step 2. Click the Name Box to the left of the formula bar.
Step 3. Type a name with no spaces, such as TaxRate or MonthlyRevenue.
Step 4. Press Enter.
You can also use Formulas > Name Manager when you need to edit, delete, or review named ranges across the workbook.
Step-by-step budget example
Say a small business budget template has monthly revenue in B5:G5 and tax rate in B20.
Step 1. Select B5:G5 and name it MonthlyRevenue.
Step 2. Select B20 and name it TaxRate.
Step 3. Total revenue with:
=SUM(MonthlyRevenue)Step 4. Calculate tax on a subtotal with:
=Subtotal*TaxRateThe formula is easier to audit because the names explain the intent. Someone reviewing the workbook does not have to chase every cell reference before understanding the calculation.
✦ TIP
Use short, specific names. Revenue is readable, but MonthlyRevenue or SubscriptionRevenue is usually safer in a real workbook.
Named ranges in invoices
Named ranges are useful in an invoice template when totals and rates need to be referenced in multiple places.
For example, if the subtotal cell is named InvoiceSubtotal and the tax rate cell is named TaxRate, the tax amount can be:
=InvoiceSubtotal*TaxRateThat is easier to read than =F18*F20, especially after rows are inserted or the layout changes.
Common named range mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Using spaces in names | Excel rejects the name | Use MonthlyRevenue or monthly_revenue |
| Giving names that are too generic | Formulas become ambiguous | Use names tied to the workbook context |
| Forgetting workbook vs sheet scope | The same name may refer to different ranges | Check scope in Name Manager |
| Leaving old names after layout changes | Formulas point to stale ranges | Audit Name Manager after major edits |
When named ranges are worth it
Named ranges are useful when an input is referenced often, when a formula needs to be readable for other people, or when a workbook has important assumptions that should be easy to find.
They are less useful for one-off formulas where the cell reference is obvious and unlikely to change. Naming every small range can make a workbook harder to maintain, not easier.
Named ranges vs tables
Excel Tables are usually better for growing data sets, such as transaction logs, CRM records, or expense rows. Tables expand automatically and support structured references.
Named ranges are better for specific assumptions, fixed input blocks, and model drivers such as TaxRate, StartDate, or MonthlyRevenue.
In a clean workbook, you may use both: tables for row-level data and named ranges for key assumptions.
The Griddy way
Named ranges help readability, but they can be tedious to set up and audit across a workbook with several sheets.
"Name the tax rate, invoice subtotal, and monthly revenue ranges, then update the formulas to use those names"
Griddy can create the named ranges, rewrite the formulas, and keep the workbook easier to review later.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.