How to Create Dynamic Named Ranges in Excel

Overview

Named Ranges can be used in formulas or a Drop Down list, to save typing the range location in each formula.

But what if more entries are needed to a Named Range? Usually, this would involve amending the named range for each change. There is no ready solution to automate this in Excel, but it can be done using some smart functions!

How to Create the Range

Let’s take an example and step by step build a dynamic Named Range in Excel:

First, create a Drop Down menu from the list below:

Example list of countries

Example list of countries

Do this first by giving this range a name and use this Named Range in the Data validation window.

First,name the range:

Select the range: A2:A6 and in the Name box (top left of the sheet) write Countries (this will be the name of the range)

Give the list a name using Named Ranges

Give the list a name using Named Ranges

If you go to Formulas in the toolbar and click on Name Manager, in the Name Manager window you will see the Named range you just created:

Enter the criteria of the named range including its location and name

Enter the criteria of the named range including its location and name

To Use this Named Range in the Drop Down menu, we will first select the cell/cells where we want our Drop Down list to appear and in the toolbar click on Data and Data Validation:

Locate the Data Validation menu

Locate the Data Validation menu

In the Data Validation window choose List in the Allow section and in the Source field enter the name of our range:

Set the Data Validation to a drop down list

Set the Data Validation to a drop down list

Click Ok and test the range in the drop down list:

The default drop down will display the existing list.

The default drop down will display the existing list.

Now if we add more countries to our list, we will not be able to see them in the drop down as our Named Range is static:

But does not display new countries when added to the list.

But does not display new countries when added to the list.

To overcome this issue, the range needs to be made dynamic, so that no matter how many countries we add it will keep on expanding.

This can be done by entering a formula in the Named Range window instead of the range itself.

Go to Formulas, Name Manager and click on the Named Range. In the Refers to field  enter the following formula:

Amend the Named Range to use OFFSET

Amend the Named Range to use OFFSET

Let’s explain:

The syntax:

How to use the OFFSET function

How to use the OFFSET function

The OFFSET function can be used without the optional arguments: height and width, the to return the value of a cell.

If we use this optional arguments then the formula will return a range and used as a standalone formula in a cell it will resolve as an error.

But used in the Named range window it will return a range, which can be used to create a range that updates depending on how many items are in the list of countries.

In the first argument of this formula: reference, enter the first cell of our range: A2. This tells Excel where the range begins.

For rows and column variables, enter 0, as this original cell A2 will remain fixed in place as the start of the list.

height – is the height of our range. Entering 6 here for example, the range would be 6 cells high, so the range would be A2:A7 ( 6 cells) .

To make this height dynamic instead of a static number for this argument use another formula which is COUNTA, which counts the number of non-blank cells in a range.

The COUNTA formula:

=COUNTA(Sheet2!$A:$A), counts how many non-blank cells are in this range.

In the initial list of countries this formula would return 6 (we have 6 filled cells in this range):

Use the COUNTA function to calculate the number of items on the list.

Use the COUNTA function to calculate the number of items on the list.

In the second instance when we added 3 more countries, this formula would return 9:

COUNTA will update to count new items

COUNTA will update to count new items

This height variable has made the height of our range (offset formula) dynamic.

In the last argument of the offset function, either leave it empty (as it is optional) or just enter 1 (the width of the range is 1 column wide)

So the formula will look like this:

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A))

Save the Named Range with the new formula and test your Drop Down, you will see that the new countries we added are displayed:

Now the drop down list is dynamic and shows new rows as they are added.

Now the drop down list is dynamic and shows new rows as they are added.

Try and add some more countries to the list and they will be immediately available in the Drop Down:

New rows will be picked up by the drop down list.

New rows will be picked up by the drop down list.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit