See all How-To Articles

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.

dynamicnamedranges table

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.

dynamicnamedranges ribbon format as table

 

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.

dynamicranges format as table

 

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.

 

dynamicnamedranges add row to table

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.

dynamicnamedranges define names

Keep the Top Row and Left column checked and click OK.

dynamicnamedranges create from selection

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.

dynamicnamedranges range names

If you select one of the range names, it will highlight the cells included in the range name.

dynamicnamedranges january

As with the table range name, you can type a formula to show the contents of the range.

dynamicnamedranges show january

 

If you add a row to the table, the named ranges will be updated to include the row.

dynamicnamedranges table create from selection

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.

dynamicnamedranges convert to range

Now if you add another row to your table, the named ranges will NOT be updated to include the row.

dynamicnamedranges add row no table

However, if you were to INSERT a row into the data, then the named ranges would be updated.

dynamicnamedranges january update

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.

dynamicnamedranges change data

 

If we now change the value for Term Desposits in cell D3, the value in H3 will also change.

dynamicnamedranges changed values

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.

dynamicnamedranges gs menu

Type in the name of the range and then click Done.

dynamicnamedranges gs create

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.

dynamicnamedranges gs array

 

If we add a row to our data, the range name will NOT be updated.

dynamicnamedranges gs add row

However, if we insert a row, the range name WILL be updated.

dynamicnamedranges insert row

 

See all How-To Articles