Settings list for validation
Named range StatusList on Settings!A2:A20 feeds every Status dropdown on intake tabs without repeating addresses.
What is a named range in Google Sheets?
A named range gives a block of cells a readable label like Revenue or ValidStatuses instead of only B2:B500. Define names from Data > Named ranges. Formulas, charts, pivot tables, and validation rules can reference the name, which makes models easier to audit and safer when rows shift inside the named block.
When to use it
Use named ranges on templates you clone monthly, validation lists, chart sources, and any formula tab where $B$2:$B$500 would confuse the next editor.
When to skip it
Skip naming every trivial one-cell constant. Too many opaque names hurt readability more than raw addresses when the sheet is throwaway scratch work.
How it works
-
1
Select a range, open Data > Named ranges, click Add a range, and type a name without spaces (use underscores if needed).
-
2
Reference the name in formulas as =SUM(Revenue) instead of =SUM(B2:B100).
-
3
Expand or shrink the named range in the Named ranges sidebar when your table grows.
-
4
Use named ranges as dropdown sources in data validation for maintainable option lists.
-
5
Prefer workbook-scoped names unless you use Apps Script that expects sheet-level scope in advanced cases.
-
6
Document names on a README tab so imports and collaborators know what each label means.
Examples in Google Sheets
Chart source that grows
Chart references named range MonthlyActuals; append rows and widen the name once instead of editing chart ranges.
Readable P&L formula
=TotalRevenue - TotalCOGS reads cleaner in board decks than a string of column letters auditors must decode.
Next step for Named range
Jump to a related Better Sheets resource, or unlock the full tutorial library.
Better Sheets resources
Common mistakes
- Names with spaces or invalid characters that Sheets rejects or that break in export tools.
- Naming a range that includes blank header rows, throwing off OFFSET and chart axes.
- Duplicate names across files after copy-paste without checking Named ranges sidebar conflicts.
- Forgetting to resize the name after inserting columns inside the block, pointing formulas at wrong cells.
- Using names that match function names like Sum or Date, which confuses formula autocomplete.
Frequently asked questions
- Named range vs defined name in Excel?
- Same idea. Google Sheets manages names under Data > Named ranges with similar formula syntax.
- Can named ranges reference other sheets?
- Yes. Include sheet prefix in the range picker when you create the name or use SheetName!A1 notation in the range field.
- Named range in IMPORTRANGE?
- IMPORTRANGE uses A1 addresses. Named ranges work inside the file that defines them, not across files by name alone.
- How do I delete a named range?
- Open Data > Named ranges, select the name, and remove it. Formulas using the name will break until you fix references.
- Named range for whole column?
- Avoid A:A unless you must. Bounded tables perform better and avoid accidental totals on stray cells.
- Dynamic named range?
- Use a formula in the range definition or pair ARRAYFORMULA tables with a fixed name on the output block you control.
- Names in Apps Script?
- SpreadsheetApp.getRangeByName('Revenue') reads named ranges in scripts bound to the file.
- Named range and protect?
- Protecting cells inside a named range still works. Editors need permission to change values the name points to.
Related Tutorials
Watch how Named range works
Create a Public Sheet and Private Sheet: Using ImportRange()
How do I reference a different spreadsheet in Apps Script?
Create a new Spreadsheet from just a Name in a Sheet.
Deep Inside Dark Habits Google Script
Can I Automatically Rename a Sheet based on a Date?
Related blog posts
Guides that explain Named range in more depth.
Google Sheets Error: Unknown Range Name and How to Fix it
A user asked me about an error, which is the "Unknown Range Name." By the time you’ve read this tutorial, you’ll know how to fix this error. What is the Unknown Range Name error? It’s an error that you get when the value that was entered does not have the correct format. Another cause of this error is when the value that was entered is outside the expected range. As you can see right here, there’s the "Unknown Range Name" error. Now, what that means is that there is supposed to be ranges ...
Read post →Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users
If you know how to code, you'll learn in this step-by-step tutorial how to code in Google Sheets.
Read post →How to Make a Small Fortune With Google Sheets | Create a Headline Generator in a Google Sheet
We're going to create a headline generator. We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets.
Read post →Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →filters in Google Sheets
Filtering hides rows that do not match criteria without deleting data. The filter toolbar on a header row is the fastest UI path. FILTER and QUERY formulas build dynamic views that update when source data changes, which suits dashboards and reporting tabs.
Read guide →dashboards
A dashboard is a single view that answers "how are we doing?" without hunting across tabs. In Google Sheets, it is usually a summary tab with big numbers, charts, and conditional formatting fed by QUERY, pivot tables, or IMPORTRANGE from raw data you keep elsewhere.
Read guide →Pivot table
A pivot table summarizes a large table without writing SUMIFS for every combination. Drag fields into rows, columns, values, and filters to count deals by rep, sum revenue by month, or average scores by classroom. The pivot updates when you refresh the source range or change the pivot cache.
Read guide →Done reading about Named range?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.