5 Ways to Use Named Ranges in Google Sheets

Want to be more efficient when working with ranges in your Google Sheets? Although this is 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 modify named ranges to include more or fewer cells. So, rather than modifying various formulas or drop-down lists to adjust cell references, you can simply update the named range and the rest will conform to it.

Second, when you use named ranges in Google Apps formulas or scripts, your syntax is easier to read. Rather than displaying cryptic cell references, you and your collaborators see the named range for better readability.

HOW TO CREATE A TABLE IN GOOGLE SHEETS USING A TABLE GRAPH

It only takes a minute to name a range of cells. Select the cells you want to name and choose 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 on “Done”.

Named Ranges sidebar to add a named range

You will notice that the cell range includes the sheet name. This allows you to use the named range throughout your workbook.

Adding links to cell ranges

For a quick way to jump to a range of cells, you can create a hyperlink. Then, with a single click, you can move directly to that range of cells. By using named ranges, you can just use that name rather than selecting the range of cells, getting the link, and then inserting the link, which eliminates some steps.

Navigate 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 link insertion window appears, optionally enter the text you want to link at the top. Next, click “Named Sheets and Ranges” at the bottom.

Named ranges available for binding

Go to the Named Range section, select the name, and your cell text will automatically link to your named range.

Linked named range

Using Names in Formulas

If you’re used to using functions and writing formulas in Google Sheets, you know that they can contain ranges of cells. By using named ranges, you can enter the name in the formula instead of the range of cells. Here are some basic examples.

You may want to add values ​​from a range of cells. If it is named, you can use that name in the SUM function formula as follows:

=SUM(Accessory Sales)

This is the equivalent of: =SUM(A1:F13) in our sheet.

SUM function formula using named range

In another example, you might want to know how many cells in your range contain numbers. Using the COUNT function, you can enter this formula with your named range:

=COUNT(AccessorySales)

This is the equivalent of: =COUNT(A1:F13) in our sheet.

Formula for COUNT using named range

Navigate to the beaches

Another benefit of 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. So you don’t need to input sheet name and cell range anymore.

First method: Select Data > Named Ranges from the menu. When the sidebar opens, click on the named range. You will be directed directly to it.

Named ranges to select

Method 2: Open the Go To function using F5, Fn+F5, or the Help menu search box. Type the named range in the Enter a range field and press Enter or Return. You go directly to the named range.

Named range in the Go To box

Insert drop-down lists

Drop-down lists are wonderful tools for data entry because they allow you to quickly and easily select an item. You can use a named range for the list.

Go to Data > Data Validation in the menu.

Data validation in the Data menu

Next to Criteria, select “List from Range” from the first drop-down list and enter the named range in the box next to it. Adjust all other necessary details for your listing 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 change cell references.

In addition to independent dropdowns like this, you’ll use named ranges when creating dependent dropdowns.

Using Google Apps Script

If you use Google Apps Script in Google Sheets, named ranges are also very useful. You can use the following to create named ranges, read from them, write to them, and even save them. This can allow you to write your scripts more fluidly and achieve better readability for yourself and others.

Create: createNamedRange()

Get: getRangeByName()

Read: readNamedRange()

Writing: writeToANamedRange()

Save: logNamedRanges()

Named ranges in Google Sheets, and even in Microsoft Excel, certainly have their benefits. If you think they might be useful to you, give them a try!

Leave a Comment