Quick Links
Want to get more efficient when working with ranges in your Google Sheets? While it's often a matter of preference, there are situations where named ranges can be beneficial and make certain tasks easier.
Why Name Ranges?
First, you can easily edit named ranges to include more or fewer cells. So rather than editing various formulas or drop-down lists to adjust the cell references, you can just update the named range and the rest will comply.
Second, when using named ranges in formulas or Google Apps Script, your syntax is easier to read. Rather than viewing cryptic cell references, you and your collaborators see the named range for good readability.
How to Name a Range
It takes only a minute to name a range of cells. Select the cells you want to name and go to Data > Named Ranges from the menu.
When the sidebar opens, confirm the cell range and enter the name you want to use in the box above. Click "Done."
You'll notice that the cell range includes the sheet name. This allows you to use the named range throughout your workbook.
1. Adding Links to Cell Ranges
For a fast way to jump to a range of cells, you can create a hyperlink. Then with a click, you can move directly to that cell range. By using named ranges, you can simply use that name rather than selecting the range of cells, obtaining the link, and then inserting the link thus eliminating some steps.
Go to the cell where you want to add the link. Click the Insert Link button in the toolbar or select Insert > Link from the menu.
When the insert link window appears, optionally enter the text you want to link at the top. Then, click "Sheets and Named Ranges" at the bottom.
Move to the Named Range section, select the name, and your cell text will be automatically linked to your named range.
2. Using Names in Formulas
If you're familiar with using functions and writing formulas in Google Sheets, then you know that many can contain cell ranges. By using named ranges, you can enter the name into the formula instead of the cell range. Here are a couple of basic examples.
Maybe you want to add the values in a cell range. If it's named, you can use that name in the formula for the SUM function as follows:
=SUM(AccessorySales)
This is the equivalent to: =SUM(A1:F13) in our sheet.
For another example, you might want to see how many cells in your range that contain numbers. Using the COUNT function, you can enter this formula with your named range:
=COUNT(AccessorySales)
This is the equivalent to: =COUNT(A1:F13) in our sheet.
3. Navigating to Ranges
Another advantage to using named ranges is that it gives you a quick way to navigate to those cells. Plus, you have two easy ways to do it from any sheet in your workbook. This eliminates the need to enter the sheet name and cell range.
Method One: Select Data > Named Ranges from the menu. When the sidebar opens, click the named range. You'll be directed right to it.
Method Two: Open the Go To feature using F5, Fn+F5, or the search field in the Help menu. Type the named range into the Enter a Range box and hit Enter or Return. You'll jump directly to your named range.
4. Inserting Drop-Down Lists
Drop-down lists are wonderful tools for data entry because they let you quickly and easily select an item. You can use a named range for the list.
Go to Data > Data Validation from the menu.
Next to Criteria, select "List From a Range" in the first drop-down list and enter the named range in the box next to it. Adjust any other details as needed for your list and click "Save."
If you need to add or remove items from your list, you can do so in the named range rather than opening the Data Validation box to edit the cell references.
In addition to independent drop-down lists like this, you'll use named ranges when creating dependent drop-down lists.
5. Using Google Apps Script
If you use Google Apps Script in Google Sheets, named ranges come in handy there as well. You can use the following to create named ranges, read them, write to them, and even log them. This can give you a smoother way to write your scripts and better readability for you and others.
Create: createNamedRange()
Get: getRangeByName()
Read: readNamedRange()
Write: writeToANamedRange()
Log: logNamedRanges()
Named ranges in Google Sheets, and even in Microsoft Excel, certainly have their benefits. If you think they'll work for you, give them a try!