Named ranges can be a great way to increase productivity. It gives cell ranges a name — meaning that instead of having to use cell addresses every time in formulas, you can use the names in formulas.
This can be fantastic for simplifying complex spreadsheets.
In this article, we will go through the process of creating a Google Sheets named range.
Table of Contents
Why Use Named Ranges In Google Sheets
There are several benefits to using named ranges in Google Sheets. You can assign your variables a unique name that helps you remember them for later use and helps others understand your code.
In short, using named ranges makes your spreadsheet easier to read and understand.
Using them makes it easier to make changes to your spreadsheet as you only need to make changes to one specified range rather than going to different places in the spreadsheet, which can get tedious.
You just need to edit the range name once, and all your formulas will use the updated range, which helps prevent future errors due to an outdated cell range reference. Named ranges also make it very easy to jump to the correct cells.
Rules to Follow When Creating a Google Sheets Named Range
To create named ranges, Google Sheets requires you to stick to some rules. Here are a few things to know when creating named ranges:
- It can’t be more than 250 characters.
- It can’t start with several “true” and “false”.
- It can’t have any punctuation or spaces. You can use an underscore instead of a space. For example, write “net_worth” instead of “net worth”. Named ranges can only have numbers, letters, and underscores in them.
- It can’t be the reference of a cell or a range. You can not name it C4 as it already refers to a cell. Similarly, you can’t name it B1:B50
Note that when you use Google Sheets named range functionality across multiple sheets to import a range, the named ranges should still be there, but sometimes, you may have to rename them.
How to Make a Named Data Range in Google Sheets
Creating a named range in Google Sheets is very simple. You can do this in four straightforward steps. To do this:
Step 1: First, highlight the data you want to create the named range for and click on Data on the top bar. From the menu, click on Named ranges. You should see a sidebar appear on the right side of the screen.
Step 2: On the sidebar, you can enter the name in the first text box, while the second one defines that range. Make sure the name you choose follows the rules mentioned above. You should be able to see the range in the second text box. If you dont, you can either type it in or use the grid icon beside the text box to choose the range.
Step 3: Once done, simply click on Done to save the changes.
Step 4: You should see that your range is saved into a list. Google Sheets makes it possible to create multiple named ranges. To do this, simply click on Add a range and repeat the same process as before.
Google Sheets can also name a single cell with the same process.
How to Edit a Google Sheets Named Range
If you need to change the name of the range or change the number of cells in it, you don’t have to delete it and go through making it again. You can edit Google Sheets named ranges by following these steps:
Step 1: Click on Data on the top bar and click on Named ranges.
Step 2: Hover your cursor over the named range that you wish to edit, and you will see a pencil icon show up. Click on it, and you should see the menu which allows you to edit the range.
Step 3: Make the necessary changes in the range and simply press Done.
Using a Keyboard Shortcut to Name Ranges in Google Sheets
To name ranges, you can also use the keyboard short Ctrl + J (Command + J on macOS). Here’s how:
- Highlight the data you wish to make a named range out of
- Press Ctrl + J which will move your typing curser to the naming box
- Type the name for the range
- Press Enter
If you’re wondering how to name a cell in Google Sheets. You follow the same process as above, but select a single cell instead of a range in step 1.
How to Use Named Ranges in Google Sheets
Now that you’re done creating a range, you’ll need to know how Google Sheets references a named range. You just need to put the range’s name as the RANGE arguments in any function that uses ranges. There are hundreds of functions that use ranges, whether they’re direct or indirect. Yet, if you want to make a dynamic named range in Google Sheets, you can use the INDIRECT function.
- To get the sum of the data in the named range, use
- To get the average of the data in the range, use
- To find the maximum value in the data, use
Here’s a sample of how Google Sheets could use named ranges in a formula. In the image below, we used the name range “onetoseven” with a SUM function:
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||What it Does|
|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|
Frequently Asked Questions
What Are Named Ranges in Google Sheets?
The named ranges feature allows you to assign keywords to a group of cells. This will enable you to input the keyword into a formula when doing calculations rather than remembering the cell address.
How Do You Use Named Ranges?
You can use named ranges by selecting the cells you want to apply the named ranges function on, and going to the Data tab, you can see the option to select Named ranges where you just have to choose the name for your range.
What Is the Advantage of Using a Named Range?
Using names for cells allows you to remember them easily rather than learning the range address. Selecting the range will show its name in the name box rather than the address of the cells.
How Do I Change a Named Range?
You can change a named range by clicking on Data on the top bar and clicking Named ranges. There, you should see a list of the named ranges. Simply hover your cursor over the named range you wish to edit. Click on the pencil icon to go to the edit menu.
What Is a Google Sheets Dynamic Named Range?
You can create a named range using a formula rather than assigning static cells. This can be very powerful for spreadsheet users as you won’t have to go and update the named range every time you add a new entry into your spreadsheet.
A Google Sheets dynamic range is particularly useful in cases where new data is input into the spreadsheet frequently, such as sales data.
Do Named Ranges Slow Down Google Sheets?
If an entire column range is selected in a named range, it will slow down google sheets as sheets will run calculations across all the cells in the row or the column, even if they’re blank. We recommend only setting up the needed cells in named range as this can dramatically increase the speed at which Google Sheets performs calculations.
Use Named Ranges to Improve Productivity
Using a Google Sheets named range is a straightforward process that allows you to be more productive. It makes your workflow more streamlined and accessible. We hope this article helped you better understand what named ranges are and how to use them in your spreadsheets.
If you learned something from this article and enjoyed it, perhaps you’d like to learn more. Check out our reviews of the best available Google Sheets courses on the web.