Dynamic Named Range Based on Cell in Excel & Google Sheets
This article will demonstrate how to create and use dynamic named ranges in Excel and Google sheets.
Format Data as a Table
Click in your data, and then, in the Ribbon, select (1) Home > Styles and then (2) select Format as Table, (3) Medium > Blue, Table Style Medium 2.
Your data will now be formatted as a table and a new tab will appear on your ribbon. You will see in the name box the that data in the table (excluding the top row if your table has headers), will now automatically given a range name.
If you now click somewhere else in your worksheet, and type in this formula:
=Table2
The contents of the table will be displayed EXCLUDING the header details as shown in the first graphic of this article.
If you now add a row to your table, the table name (range name) will be updated to include that row.
Create Named Ranges from Selection
While still keeping your data in a table format, you can add other named ranges to the table.
Highlight your entire table, and then, in the Ribbon,select Formulas > Defined Names > Create from Selection.
Keep the Top Row and Left column checked and click OK.
If you then click in the Name box, you will see the range names that have automatically been created, includuding the name of the table.
If you select one of the range names, it will highlight the cells included in the range name.
As with the table range name, you can type a formula to show the contents of the range.
If you add a row to the table, the named ranges will be updated to include the row.
You can also use this method to add range names when your data is NOT formatted as a table. However, if you had removed the table format and converted your data to a range, then the range name would NOT be updated if you add a row to your data.
In the Ribbon, select your data, and then, select Table Design > Tools > Convert to Range.
Now if you add another row to your table, the named ranges will NOT be updated to include the row.
However, if you were to INSERT a row into the data, then the named ranges would be updated.
One of the advantages of using named ranges in formulas, is that if the data in the range name changes, the updated data will be carried through to wherever the range name has been used.
If we now change the value for Term Desposits in cell D3, the value in H3 will also change.
Dynamic Named Ranges in Google Sheets
Google sheets does not have the ability to format the data as a table, but you can create range names for your data.
Highlight the cells you wish to create a range name for, and then, in the Menu, select Data > Named ranges.
Type in the name of the range and then click Done.
We can use this range name in an ARRAYFORMULA.
Type this formula into a new cell:
=ARRAYFORMULA(January)
The contents of the range name will be inserted into your sheet.
If we add a row to our data, the range name will NOT be updated.
However, if we insert a row, the range name WILL be updated.
