How to Create Dynamic Named Ranges In Google Sheets

We often use named ranges in our google sheets and one of the key advantages of them is that instead of utilizing cell references, you can give your columns descriptive names which makes formulas easier to understand.

However, one step ahead, you can also use dynamic named ranges which means you can use a formula to create a specified range. This can be a very useful tool and therefore here is an article to make you learn about its usage.

How to create a dynamic named range

Let’s go ahead by understanding through an example datasheet.

named range example

Here we select column D to create dynamic named range and for this –

  • The first step will be to build a formula to count data for which dynamic named range will be created. According to above example, following formula should be used in cell F2 =COUNT(D2:D1000)+1 this formula will count the number of cells in which there are numbers. Note – here range is extended to D1000 in order to ensure continuous count upon entry of any new data.
  • Next we need to create a reference for our sales column. In cell G2, I have used the formula: =“Sheet1!D2:D”&F2.
dynamic range-2
  • Now, it would show the exact reference that your data is through, once you enter the formula in previous stage. I have 9 rows in column D, so my formula is visible through D9.
dynamic range-3
  • After this, we will create a name range by selecting data option from the menu bar and then selecting named ranges from the drop-down menu.
dynamic range-4
  • I’ll keep total sales as the name of my range. For data range, you want to put the location of the cell with your reference formula in step 2 for the data range. Since my cell was in G2 I’ll use it. Now click Done when you have input both of these.
dynamic range-5
  • It’s now time to build up your dynamic named range. With INDIRECT function you may start using it with formulas. Here, i have used formula

=SUM(INDIRECT(TotalSales)) to show the sum of the entire column.

dynamic range-6
  • The overall sum of my sales column is D, as you can see.
dynamic range-7
  • Because this range is dynamic, it can be seen on how adding values to the columns causes INDIRECT sum function to increase.
dynamic range end
  • And here, we are done with our task and a new learning.

Harshita Mathur

My name is Harshita Mathur. I come from Jaipur, Rajasthan. I am a law student. Apart from this I am an amateur writer.

Recent Content