How to Create Named Ranges in Google Sheets (A Step-by-step Guide)

Using named ranges is a great way to simplify complicated spreadsheets and get more done. You can assign a name to a range of cells in Google Sheets. So, when using formulas, everything remains tidy, and keeping track of them is less demanding.

For example, suppose you have sales data in your spreadsheet and want to calculate metrics such as maximum or minimum sale value, the sum of all the sales, the count of sales of particular items, and the average sale value.

When you have a sheet with several formulas, entering several cell references won’t be necessary. The idea is to create a summary sheet with similar calculations and use named ranges to make your work easier.

We will take you through naming cell ranges in Google Sheets and how to use them in different beneficial situations.

Why Use Named Ranges In Google Sheets

One of the many benefits of using named ranges in Google Sheets is that it adds clarity to your spreadsheet. When variables are assigned a unique name, remembering it is much easier when you return to the work for later use.

Moreover, other people also benefit from it as it your formulas easier to read and understand.

When you want to modify your spreadsheet, rather than going to different places in the spreadsheet, you save yourself time and energy by making changes to one specified range.

It doesn’t end there. Suppose you have used a formula for a particular range, and you want to extend the range, just edit the range name once. Every one of your formulas will use the updated range.

Named ranges are very useful in Gooogle sheets. It would be best if you used it because it helps prevent future errors, makes making changes to your spreadsheet easy, and makes jumping to the correct cells faster.

The most important reason we recommend getting good at using named ranges is that it cuts away tedious work and lets you cover more ground.

Rules to Follow When Creating a Google Sheets Named Range

To create named ranges in Google Sheets, there are some rules you will have to stick to. Think of them as guidelines because they ensure your named ranges follow a similar pattern.

To create named ranges, these are the rules you are required to follow:

Your named range must be at most 250 characters. Using a named range is to simplify things, so 250 characters for a name is more than enough.
The name you choose must not start with several “true” and “false.”
Named ranges can only have letters, numbers, and underscores in them. You can’t include any punctuation or spaces. For example, instead of using space to write “net worth,” you can replace the space with an underscore and have it like this “net_worth.”
The point of named ranges is to assign a different name to cell ranges. So your named range must not be in reference to a cell or a range. Names like C4 and B1:B50 already refer to a cell, so choose an actual name or number.
Keep in mind that when you have multiple sheets with named range functionality in Google Sheets. The named range of any imported range should still be there, but in some cases, renaming them will still be necessary.

Creating a Named Range in Google Sheets

The following steps are the steps you can use to create a named range in Google Sheets:

Step 1: Got to Data > Named ranges.

Step 2: Select the range of data you want to create a named range for.
Step 3: Click on data from the toolbar at the top.
Step 4: Select Named ranges, and a sidebar should appear on the right side of your screen.

Step 5: Enter the name you want to assign to the selected range in the first text box. The range you selected will appear in the second box; if it doesn’t, use the grid icon beside the text box to choose your preferred range or type it in the box.

Ensure that the name you decide on follows all the rules previously discussed. Otherwise, you get a “specified range not valid” prompt when you want to save the setting.

Since you can see your selected age range in the second box, adding or removing cells while keeping the named range is easier.

Step 6: Click on Done to save the changes.

Your range will successfully save to a list. Google Sheets allows you to create multiple named ranges. So if you want to add more named ranges, you only have to click on “Add a range” from the name range side menu and repeat the same process we used in creating the first one.

How to Edit a Google Sheets Named Range

Suppose you have a named range that needs to be updated by including additional data. Google sheets let you change the number of cells in the range or the name of the range without having to delete it and go through making it again.

For example, if you have assigned a named range for credit inflow on your spreadsheet and have three new transactions you want to add to the data. You can include the additional data points by updating the named range.

Modify your Google Sheets named ranges with the following steps:

Step 1: Click on Data on the toolbar at the top and select Named ranges.

Step 2: Hover your cursor on the name range you wish to alter.

Step 3: Click on the pencil icon that shows up. You will now be able to edit the selected range.

Step 4: Click Done after making the necessary changes to the range.

Adding Links to Cell Ranges

Creating a hyperlink lets you jump faster to a range of cells. You can jump to the cell range you’ve linked with just a click. Rather than selecting the range of cells, you can use named ranges, obtain the link, and insert the link to save yourself some steps.

Step 1: Select the cell you want to add the link to.
Step 2: Click Insert from the toolbar.
Step 3: Select Link or use the shortcut Ctrl+K from the cell you want to link.

Step 4: Click “Sheets and Named Ranges” from the insert link menu at the bottom.

You’ll see a list of your already-named ranges, and you can also select a new range of cells to link.

Step 5: Select the name, and your cell text will be automatically linked to your named range.

Inserting Drop-Down Lists Using Named Ranges

One of the best tools for data entry is drop-down lists. They are handy because they let you quickly and easily select an item.

You can use a named range for your list with the following steps:

Step 1: Click on Data from the toolbar menu
Step 2: Select Data Validation from the menu.

Inserting Drop-Down Lists Using Named Ranges

In the drop-down box next to Criteria, select “List From a Range” and enter the range or formula in the box next to it. Add any other details you need for your list and click “Save.”

The good part is you don’t have to open the Data Validation box to edit the cell references in the range you entered. You can add or remove items from your list in the named range.

You’ll use named ranges when creating dependent drop-down lists in addition to independent drop-down lists like this.

Using Google Apps Script

Named ranges can also come in handy when using Google Apps Script in Google Sheets. This can give you an easier way to write your scripts and better readability for you and others.

You can use the following commands to create named ranges, write to them, read them, and even log them.

Create: createNamedRange()

Read: readNamedRange()

Get: getRangeByName()

Write: writeToANamedRange()

Log: logNamedRanges()

Using a Keyboard Shortcut to Name Ranges in Google Sheets

Shortcuts encourage faster workflow. All you need is to master the shortcut of a particular functionality, and with time, you won’t have to waste too much time using it for your data.

Ctrl + J or Command + J (on macOS) is the keyboard shortcut for named ranges.

Use the following steps to set it up:

Using a Keyboard Shortcut to Name Ranges in Google Sheets

Step 1: Highlight the cells, including the data you wish to name
Step 2: Press Ctrl + J, and your typing cursor will appear on the Name box
Step 3: Type in the name you want for the highlighted range
Step 4: Press Enter

Note that the same process above can be used to name an individual cell in Google Sheets. Select a single cell instead of a range of cells and follow the steps.

How to Use Named Ranges in Google Sheets

Knowing how to create a named range is just the beginning. One of the reasons why named ranges are such an invaluable feature in Google Sheets is that they let you reference the name as a function.

You only need to put the range name as the RANGE argument for any function that uses ranges. Now, lots of functions use ranges, whether they’re direct or indirect.

Suppose you want to make a dynamic named range in Google Sheets, it’d be a good idea to use the INDIRECT function.

For example:

To sum up, all the data in a specified named range, use the following:

=SUM(name_of_range)

To find the maximum value in the data, use

=MAX(name_of_range)

To get the average of the data in the range, use

=AVERAGE(name_of_range)

The sample data in the image below is an example of how you can use named ranges in a formula in Google Sheets. We use the name range “onetoseven” with a SUM function:

How to Use Named Ranges in Google Sheets

All we did was create a named range for cell A1:A7 and call it “onetoseven”. We now inputted the function =SUM(name_of_range) in cell C3 to give us the sum of all the data in our named range.

Using Scripts with a Named Range In Google Sheets

You can create automation using Google App Scripts for named ranges. To interact with named ranges, you can use these codes.

Code	Function
getName()	Fetches the name of the range
getRange	Finds and displays the range referenced by the name
remove()	Deletes the named range
setName(name)	Creates or changes the name of the range
setRange(range)	Creates or changes the range related to the name

Creating a Dynamic Named Range in Google Sheets

Named Ranges are an excellent option for updating a range of cells and all the formulas tied to those cells. Updating data manually can seem a bit tedious, so Google sheets allow you to simultaneously modify all the formulas in a particular named range.

The only downside is when you have too many named ranges, it can be a bit of work to update the data.

While it’s possible to create a named range using the OFFSET or INDIRECT formula in Excel, Google Sheets doesn’t allow you to do that.

However, you can use an INDIRECT function trick to get what you want.

Let’s say you have a dataset, as illustrated below, and you want to create a named range for part of the data such that the named range automatically updates when new data is added.

The following steps are how to create a dynamic named range in Google Sheets:

In cell E2 above, we enter the formula: =COUNT(C2:C100)+1

So that we can get the number of cells with numbers in them, which is also why we’re using the COUNT function, as all the data involved is numeric. You can use the COUNTIF as well. It all depends on the type of data you have.

1 is added to the formula as our sales data starts from row number 2. We also used C2:C100 so that when we add data, later on, it will automatically be counted.

Creating a Dynamic Named Range in Google Sheets

In cell F2 above, we entered the formula =“Sheet1!C2:C”&E2

The formula will provide us with a reference to help cover the data column we’ve selected. If, for example, we have 10 transactions, it would give Sheet1!C2:C11. 15 transactions will give Sheet1!C2:C16 in return.

Click on the Data from the toolbar at the top and select Named Ranges.

Create a named range for the range Sheet1!F2 and name it SalesData

To refer to the dynamic named range, use the formula: =INDIRECT(SalesData)

The INDIRECT function would use the named range and refer to cell F2, which in turn has the reference for the sales data. Since we have made the range in F2 dynamic (by using = “Sheet1!C2:C” &E2), the named range also becomes dynamic.

For example, suppose you want to sum up sales. The formula =SUM(INDIRECT(SalesData)) can help you calculate the sum. If more sales transactions are recorded along the line, the formula will automatically update and provide you with the new sum of sales.

The Use of Named Ranges in Vlookup in Google Sheets

There are lots of benefits involved in using the combination of Vlookup and Named Ranges in Google Sheets. However, the most important is to make the formula look cleaner.

We’ll use the sample data below to explain how a Named Ranges + Indirect + Vlookup combination works.

The Use of Named Ranges in Vlookup in Google Sheets

In the sample data (Range A2: C5) above, we have a price list of industrial items in columns A, B, and C, or you can say A2:C.

Column B contains the description of the items. In contrast, column A has the code of each item in B. Item codes are generally used to categorize similar items to make tracking easier in a warehouse tracking system.

We have the unit price of the items in column C.

Search Key:

There’s a search key in cell E3, the code of one item we have listed.

Expected Formula Output:

Now, we’ll search for one of the codes in column A in the (E3) column, and if found, return the corresponding unit price from column C.

We can either return the unit price with or without using Named Ranges.

Vlookup Without Named Ranges

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

Formula:

=vlookup(E3,A2:C5,3,0)

In F3, we entered: vlookup(search key [“RBL1”] in cell E3,lookup range[A2:C5],column number that contains the price of item [3],exact match[0])

Vlookup With Named Ranges

Our sample data above ranges from cell A2: C5, which is our lookup range in Vlookup. What we’ll do is assign a particular name to that range.

Here are the steps to do so:

1. Select the data in the range A2: C5.
2. Click on Data from the toolbar, and select Named ranges in the menu that pops up.
3. Type in “pricelist” or any name you want the cell range to bear
4. Click “Done.”

Now let’s use our newly created Named Range in the Vlookup formula.

Formula:

=vlookup(E3,pricelist,3,0)

We replaced A2: C5 with “pricelist,” which is our lookup range.

Frequently Asked Questions

How Do Named Ranges Work in Google Sheets?

Named range in Google Sheets simply assigns keywords to a group of cells. Apart from making your spreadsheet easy to read and specific data easy to recollect, it makes adding data to an already formulated range of cells easier.

How Do You Use Named Ranges?

All you need to do to use name ranges is select the cells you want to name, click on the Data tab, and you will see the Named ranges option. Just input the name you want, and the named ranges function will be applied to the already selected range.

What Are The Benefits of Using a Named Range?

Instead of having to learn several range addresses, named ranges lets, you remember a specific range of cells. Rather than the address of the cells, the assigned cell range name will show in the name box when you select the range.

How Do I Change a Named Range?

Add changes to your named range by clicking Data>Named ranges. There would be a list of the named ranges from the named range side menu that pops up. To edit a particular named range, hover your cursor over it till the a pencil icon show up for you to click on to go to the edit menu.

What Is a Google Sheets Dynamic Named Range?

Other than assigning static cells, another way to create a named range in Google Sheets is by using a formula. It’s very beneficial for spreadsheet users because every time you add a new entry to your spreadsheet, there won’t be a need to update the named range.

Do Named Ranges Slow Down Google Sheets?

When you select a range of columns in a named range, Google Sheets will slow down as it tries to run calculations across all the cells in the row or the column. It doesn’t even matter if they’re blank.

It is recommended that you only set up a named range for the cells you need to help you increase the speed at which Google Sheets performs calculations.

Can Google Sheets Named Range Improve Productivity?

You can use Google Sheets named range to make your workflow more streamlined and accessible. It is a very straightforward process that can help improve your productivity.

Final Thoughts

A Google Sheets dynamic range is particularly useful in cases where new data is input into the spreadsheet frequently, such as sales data, price lists, etc.

You have most likely encountered the pain of writing or copying a formula you have already written in Google Sheets, only to discover that the cell references got messed up.

Another issue Named ranges can help you solve comfortably is having to type in references repeatedly after highlighting a large set of cells.

Whether creating dynamic named range formulas, utilizing Vlookup formulas in a range of cells, or using the named range with google app script, using a named range in Google Sheets will make your life a lot easier.

We hope this article helped you better understand what named ranges are and how to use them in your spreadsheets.