Using named ranges in your spreadsheets can help you stay organized and make your file easier to understand.
One of the main benefits of named ranges is that you can create descriptive names for your columns instead of using cell references, which helps make your formulas easier to understand.
If you want to take this one step further, you can create dynamic named ranges in Google Sheets. This means that you can set up your named range to be based on a formula. This can be a powerful tool for spreadsheet users.
In this tutorial, I will show you how to create a dynamic named range in Google Sheets.
How to Create a Dynamic Named Range
The trick to creating dynamic named ranges in Google Sheets is using the INDIRECT function.
In Excel, you can actually create your named range using the INDIRECT function, but this doesn’t really work in Google Sheets, but there is a trick for using this function with your named range.
The benefit of doing this is that your named ranges will automatically update and so all the formulas using them will also update automatically. Renaming ranges can be extremely tedious if you have many of them.
To show you how to create a dynamic named range, I am going to walk you through creating one with this example dataset:
I will be creating a dynamic named range in column D.
Here are the steps:
1. The first step is to create a formula to count the data that we want to create the dynamic named range for. In cell F2, I have entered the formula: =COUNT(D2:D1000)+1. This formula counts the number of cells that have numbers in them. The range here goes all the way to D1000 so that when new data is added it will still count.
2. Next we need to create a reference for our sales column. In cell G2, I have used the formula: =“Sheet1!D2:D”&F2.
3. After you enter the formula in the previous step it should be showing the exact reference that your data is through. In column D, I have 9 rows, so my formula is showing through D9.
4. Next we will actually create the named range. Go to the top menu and select Data, then select Named ranges.
5. I am going to title my named range “TotalSales”. For the data range, you want to put in the location of the cell that has your reference formula in step 2. Mine was in G2, so I am using that cell. Select Done when you have both these entered.
6. Your dynamic named range is now set up. You can begin to use it with formulas with the INDIRECT function. In my file I have used the formula =SUM(INDIRECT(TotalSales)) to show the sum of the entire column.
7. You can see here the total sum of my sales column is D
8. Since this range is dynamic, watch as add values to the column and my INDIRECT sum function automatically increases.
In the above example, I have walked you through the steps to create a dynamic named range and shown you have using the INDIRECT function to reference this range can create formulas that automatically update when your data changes.
This is a very useful skill to learn, so take your time to really understand what is being done here and why it works.
Please reach out if you have any questions. We hope you found this helpful!