How To Create Named Ranges In Google Sheets

Working with vast chunks of data can be a challenging task for most individuals. Microsoft Excel or Google Sheets users have to work with different formulas where they need to use data ranges. If you face difficulties in remembering these ranges, you can use named ranges in Google Sheets for better understanding.

For instance, we have a list of products, and we wish to retrieve only a limited number of products using the QUERY function in Google Sheets. Instead of selecting a range of cells, say A1:B11, you can choose to name that Google sheet range as ‘Products’ and use it in the formula. Data comprehension becomes easier when you use named ranges in a spreadsheet. Moreover, you can also use a named range in formulas in a Google spreadsheet to have cleaner formulas for better understanding.

How To Create Named Ranges In Google Sheets: Shortcut To Clear Formulas

Naming a cell range reference in Google Sheets is a simple process. It’s not mandatory, but if you opt for it, you won’t be disappointed for sure. Let’s look at how to execute this process.

Note: These steps will give you the basic information on creating a Google Sheets named range. Now, let’s look at the step-by-step process to create named ranges in Google Sheets in detail with images.

How To Create A Named Range

Creating named ranges in Google Sheets is a simple process. For instance, let’s say we have this sales datasheet. The job is to give an appropriate name to this spreadsheet range so that referencing becomes easier.

Open the Google Sheets data you wish to name and select the spreadsheet range.

Click on the Data tab in the menu bar and choose Named Ranges from the drop-down menu.

Now, you will see a Named Ranges sidebar on the right-hand side. Specify the range and give it a proper name as shown in the image given below.

Click on Done once you are finished with the process.

This is how you create a Google Sheets named range. There are certain rules to follow while selecting names for ranges which we will look into in the next section.

Rules For Naming Ranges In Google Sheets

Before you go ahead with the naming process, make sure you follow the rules mentioned below.

1. There should be no space in the names of ranges; you can use an underscore(_) to indicate separate words. For instance, it should be ‘Sales_Sheet’ instead of ‘Sales Sheet.’

2. You cannot use the words ‘True’ or ‘False’ in the name.

3. A name cannot be more than 250 characters long.

4. You cannot have any cell references as names. For instance, you cannot name the data A1 just because your data range starts from that cell.

How To Edit Google Sheets Named Range

The named range feature isn’t dynamic; you need to update the range if you add any new records to a data range you have already named in the past. In this case, you need to add those new records and edit the name manually.

To begin with, go to the Data tab from the menu bar and click on Named Ranges from the drop down list of options.

In the right sidebar, click on the Pencil icon next to the named range to start editing.

Now, edit the range and add the new records. Click on Done to save the changes.

Every time you add new records to a data range, you need to edit the named range in Google Sheets in this manner.

How To Create A Dynamic Range In Google Sheets

You can edit named ranges as and whenever you want, however, it would be a tedious task if you have to update the range every day in a worksheet. A dynamic solution is required to make sure all the added new records are automatically included in the Google Sheets named range.

You can use the INDIRECT formula or OFFSET Excel formula to create this dynamic solution in Microsoft Excel, but you don’t have this facility available in Google Sheets. However, there is still a workaround that will allow you to create a Google Sheets dynamic named range.

Create A Dynamic Range Manually

To begin with, choose a data range manually in a workbook. You have to make sure that the cell range you choose has to be larger than the actual range in which your data is stored. For instance, in the sheet given below, the data range is A2:F21. So, we will select the cell range A2:F100.

Why do we need to do this? Because when the data is added to the data table in the future, it will be counted automatically since we have specified a larger range. Now, you need to use this data range in the COUNT function as given below. We have entered this Google Sheets formula in cell H2.

=COUNT(A2:F100)+1

The COUNT function will give us the number of cells that contain a numeric cell value in it.

Enter Formula To Cover The Entire Column

Next, select another single cell and enter the formula given below in the formula bar by modifying it according to your requirements.

=“Sheet1!F2:F”&H2

This formula will allow us to cover all the data in the Revenue column. For instance, if there are 13 records, the formula will be Sheet1!F2:F14. If there are 19 records, the formula will be Sheet1!F2:F20. We will enter this formula in cell I2.

Create Google Sheets Named Range

To create a named range, follow the same steps as we saw in the previous examples. In this example, we need to name the range Sheet1!I2.

Use Indirect Function

Next, use the INDIRECT function in the manner given below.

=INDIRECT(Revenue)

The INDIRECT function would take into account the named range in Google Sheets and refer to cell I2. Now, since the data range in I2 is already dynamic, the INDIRECT function also becomes dynamic.

For instance, if you wish to calculate the average revenue, you can use the formula given below.

=AVERAGE(INDIRECT(Revenue))

This will give you the average revenue figure. If you add more records to the data range, the average figure will also count those new records and update the final value in the blank cell automatically.

How To Create Named Ranges In Smartphones/Google Sheets App

Unfortunately, you cannot create named ranges in Google Sheets mobile app. You can only view the named ranges on a spreadsheet that has been created on a PC.

For instance, we have named the selected range in this datasheet as Sales; the actual range is A1:G12. Let’s see how we can view it on mobile.

To view the named ranges in Google sheets app, open the spreadsheet.

Then, tap on the three vertical dots in the top right corner.

Next, choose Named ranges from the drop-down menu options.

You will now be able to see which ranges have been named on that particular spreadsheet.

You can name multiple ranges while working with complex formulas like the Array formula, VLOOKUP function in Google Sheets, or while using a simple function like the IMPORTRANGE function. Also, you can use this while doing data validation.

Final Thoughts

When we work with complex datasheets, it is essential to remember the data range in which the data is stored. However, if the data is very large and you need to refer to it entirely, you need to scroll down to the very end to get the cell range for using it in a formula. Instead of going through this hassle every time, you can use named ranges in Google Sheets to reference data quickly.

The range names feature isn’t dynamic. It means when you add new records to a data table that you have already named, you need to go back and edit the table range to fit in the new records. Instead, you can use the workaround mentioned in this article to ensure new records are added to the named range automatically. Also, you cannot name a list of ranges at once; Google Sheets allows naming only one range of cells at a time.

FAQs

What are ranges in Google Sheets?

Ranges refer to a selected group of cells on which a particular operation is being performed by the users.

Can I use a conditional formatting rule in a named range in Google Sheets?

No; you cannot use a conditional formatting rule in a named range.

How do I create a dynamic range in Google Sheets?

There is no direct way to create a dynamic range, so you need to create it indirectly using the INDIRECT function.

Can we use named ranges in Google Forms?

No, you cannot use a named range in a Google form.