Quick Links
Named ranges are a common feature of spreadsheet software. But it’s quite possible you haven’t even heard of them, despite their usefulness. Find out why named ranges are so convenient and how you may use them to clean up your spreadsheets.
What Are Named Ranges in Google Sheets?
A named range is a bit like a variable in programming. Instead of referring to a range of cells like A1:A4, you can refer to them using a name of your choice, like “payments.” The feature is not very well known, but is present in mostspreadsheet software, including Microsoft Excel.
Named ranges have several benefits. For a start, they can help to document your spreadsheet and make for cleaner formulas, likeSUM(Payments)instead ofSUM(A1:A4). As soon as you see that formula, it’s obvious what it does, without you needing to hunt elsewhere in the spreadsheet.
You can also use named ranges for maintainability. If you use a specific range more than once, referring to it by name makes it easier to change later. Instead of changing “A1:A4” everywhere you use it, just change the “payments” range from “A1:A4.” Then, every formula that refers to “payments” will be up-to-date.
How Do You Use a Named Range?
You’ll need to start with a range of data, but be aware that this can include a single cell.
There are various rules about which characters you may use in your names. Stick to a-z letters without punctuation, and you shouldn’t have any problems. If you do use an invalid name, you’ll get a useful error message: “The name given to this range is invalid.”
Create a Named Range
After you type the name, make sure to pressEnter. Clicking out of the box will cancel the named range.
Use a Named Range in a Formula
In a formula, wherever you might use a range, you can now use a name you’ve defined instead.
ensure your named range exists before you try to use it in a formula. If not, you’ll see an “Unknown Range Name” error and you may need to wipe the formula and start again.
Edit a Named Range
You can change the name of a range at any time, even if you’re already using it. Simply select the range and change the value in the Name box. If you rename a range, Sheets will automatically update any formulas that use it.
View All Named Ranges
Sheets does not display a name unless you have the exact range selected, so it’s easy to lose track of them. If you need a quick overview, just follow these steps.
