How to create a Dynamic Named Range In Google Sheets

How to create a Dynamic Named Range In Google Sheets
Reading Time: 5 minutes

In this article, we would have a look at Dynamic Named Range in Google Sheets. We will implement it in a step-by-step fashion using a simple example.

What is Named Range?

Named range is a feature in Google Sheets that can be used to give a name to a range of cells. Basically, if I have a range B1:B20 which consists of sales records, then I can define this range and give it a name, let’s say SalesRecordRange. If I were to find the sum of these numbers, I can type both =SUM(B1:B20) or SUM(SalesRecordRange) to get the sum of the sales data.

What is Dynamic Named Range in Google Sheets?

Dynamic Named Range in Google Sheets is a range which is based on a formula. For example, we have a shopping list in which we are constantly adding entries. We also have a SUM formula which calculates the sum of the cost of the items on the shopping list. We want the SUM to update accordingly if there is any entry or deletion in the shopping list. 

Sample of a shopping list
Figure 1: A sample shopping list

This can be achieved using the Dynamic Named Range in Google Sheets. We simply have to declare the shopping list in a dynamic named range, and then use the SUM function to calculate the sum for that range. The Dynamic Named Range would change its value if there is an addition or deletion.

There is no direct way to implement a Dynamic Named Range in Google Sheets.

The dynamic named range is much easier to implement in Excel. There is no direct way to implement Dynamic Named Range in Google Sheets. There is a trick using the INDIRECT function that we will use to implement it.

How to implement Dynamic Named Range in Google Sheets?

Suppose you have a dataset as shown above. We want to create a named range in a way such that wherever we add any value to the shopping list, the range gets updated. Below are the steps you can follow to implement Dynamic Named Range in Google Sheets

Step 1. Use the COUNT function to count the number of valid entries dynamically

The COUNT function returns the number of numeric values in a range. We can use this function on the “Price” column to return the number of entries in the shopping list.

Alternatively, you can use COUNTA if you also have string values. If you have any other conditions, you can try using COUNTIF.

Finding number of entries in the shopping list
Figure 2: Finding the number of entries in the shopping list

We used the formula =COUNT(B2:B1000)+1 . COUNT(B2:B1000) would return the number of valid integers between B2 and B1000. So, if we add another entry, it would get incremented. B1000 acts as the upper limit here. If your data can have more rows, then you can change this value as per the situation. Finally, we added 1 to the function since we wanted the index of the last entry to define a named range. The last index would always be one greater than the number of entries since our entries start from 2. 

Next, we would have a look at how to implement named ranges dynamically.

Step 2. Create a reference for the Given Range

In the previous step, we dynamically calculated the position of the last cell. Now we want to reference the range of the sales data. We can do this by using the following formula.

=”Sheet1!B2:B”&E3

Formula for the reference for the shopping list
Figure 3: Formula for the reference for the shopping list

This would return the dynamic range for the Price attribute in the Shopping list. If you enter this formula, it would automatically get converted to the current range.

Resultant reference
Figure 4: Resultant Reference

As you can see in this image, the formula shows the result “Sheet1!B2:B8” automatically. If we add another entry, it would get updated to Sheet1!B2:B9.

Step 3: Make a Named range for the Shopping List with the dynamic range we just created

  • Click on the Formula Cell(In this case E6)
  • Go to Data>Named Ranges
  • Click “Create New range”
  • Give a name to your range. We will be naming our range “TotalPriceRange”
  • Click Done
Named range setup
Figure 5: Named Range Setup

Step 4: Use the INDIRECT function to refer to your dynamic range

Now everything is set up and we just need to calculate the sum. We can do so by using the given formula.

=SUM(INDIRECT(TotalPriceRange))

The INDIRECT function basically returns the cell reference specified by a string. Our dynamic range is a string value, if we were to directly reference it inside the SUM then it won’t work. That is why we need the INDIRECT function to return the reference. The SUM function simply returns the sum over that range.

Dynamic named range in Google Sheets
Figure 6: Dynamic Named Range in Google Sheets

Let’s add another value to the shopping list.

Testing dynamic named range in Google Sheets
Figure 7: Testing Dynamic Named Range in Google Sheets

We added Ice cream to the shopping list and everything automatically got updated. Just keep in mind to either not exceed the limit or keep the limit high enough. 

Conclusion

We saw how to implement Dynamic Named Range in Google Sheets using the INDIRECT function. It is ideal for the cases when there are additions and deletions to your data frequently. We also saw an example of the same.

See Also

Loved this article on Dynamic Named Range in Google Sheets? Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets in a simple step-by-step 2 min guide.

How To Calculate Standard Deviation In Google Sheets: We will learn how to calculate standard deviation in Google Sheets using the STDEV function. The STDEV formula is used to find the standard deviation of numbers or a range of values.

How to Calculate Running Balance in Google Sheets: Running balance is a total of transactions already made, or in other words, a cumulative total. In this article, we would have a look at how to implement the same.

2 responses to “How to create a Dynamic Named Range In Google Sheets”

Leave a Reply