Technology

5 Ways to Use Named Ranges in Google Sheets

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.

Named Ranges in the Data menu

When the sidebar opens, confirm the cell range and enter the name you want to use in the box above. Click “Done.”

Named Ranges sidebar for adding a named range

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.

RELATED: How to Get a Hyperlink to a Cell or Range in Google Sheets

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.

Link in the Insert 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.

Name ranges available for linking

Move to the Named Range section, select the name, and your cell text will be automatically linked to your named range.

Linked 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.

RELATED: 9 Basic Google Sheets Functions You Should Know

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.

Formula for SUM using a named range

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.

Formula for COUNT using a named range

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.

Named ranges for selection

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.

Named range in the Go To box

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.

Data Validation in the Data 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.”

Named Range for a drop-down list

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.

RELATED: How to Create a Dependent Drop-Down List in Google Sheets

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!



File source

Tags
Show More

Related Articles

Back to top button
Close