How to Create a Dynamic Named Range

How many times have you wanted to add things to a list in Excel without needing to change formulas or update cell references? Named Ranges are excellent for this because they allow you to define a specific name for your range of cells and then reference the name throughout your spreadsheet. If you want to add values to your named range, all you have to do is go into the Name Manager dialogue (Windows) or the Define Name dialogue (Mac) and update the reference to your range of cells once. However, what if you could add values to your named range, and the reference to your range of cells was updated automatically without going into the Name Manager dialogue (Windows) or the Define Name dialogue (Mac)? This is where Dynamic Named Ranges come in.

In this blog post, we are going to look at how to create and use Dynamic Named Ranges. There are several ways to create dynamic Named Ranges, but in this blog post, we look at a method that uses the OFFSET and COUNT, or COUNTA functions. Will also look at how to use Dynamic Named Ranges with drop-down menus.

If you’re not familiar with Named Ranges, check out my blog post by clicking here.

You can download the example spreadsheets used in this blog post by clicking the button below or by clicking the link here.

Quick Example

Let’s start with a quick example. In this example, we are going to use a Dynamic Named Range to hold a reference to a range of cells that contain a list of fruit. In cell C2, we can type the name of a type of fruit in our list, and in cell D2, it will tell us the index position of the piece of fruit in our Named Range.

To start with, we type a list of fruits in cells A1:A9.

List of Fruit

Now, type Orange in cell C2.

Orange Index Position 2

As you can see, the formula in cell D2 returns the value 2. This is because Orange is in the second row of our Named Range.

Now, if we add a new fruit to our list in cell A10, it is automatically added to our Named Range.

To prove this, we can type Mango in cell A10.

Add Mango to Cell A10

Then, in cell C2, we type Mango and press Enter.

You should now see the value 10 in cell D2.

Mango Index Position 10

To add a new item to our list of fruits, all we need to do is add it to the bottom of our list; there are no formula changes.

Functions

In this next section, we are going to take a look at the functions that are required to create a Dynamic Named Range. Two functions are required to create the formula used to create a Dynamic Named Range: the OFFSET function and the COUNT function if your Dynamic Named Range is made purely of numbers. If your Dynamic Named Range is made up of a mixture of text, numbers, and symbols then the OFFSET function and the COUNTA function is required.

OFFSET Syntax

The OFFSET function returns a reference to a cell or a range of cells that is offset by a certain number of rows and columns from a cell or range of cells. The value that is returned by the OFFSET can be a cell reference or a range of cells.

Optionally, if returning a reference to a range of cells, the OFFSET function enables you to specify the height (rows) and width (columns) of the reference that is returned.

The syntax of the OFFSET function is OFFSET(reference, rows, cols, [height], [width])

ParameterDescription 
referencethe reference parameter is the starting reference (point of origin) that the offset will be calculated from. The reference parameter must be a cell or range of cells. If an invalid reference is provided, the OFFSET function returns the #VALUE! error message. This parameter is required.
rowsthe rows parameter is the number of rows (up or down) to the offset cell reference or the upper-left-hand cell of the range of cells.
The rows parameter can either be a positive or negative number. If it is negative, then the offset row is above the reference parameter; if it is positive, then the offset row is below the reference parameter. This parameter is required.
colsthe cols parameter is the number of columns (left or right) to the offset cell reference or the upper-left-land cell of the offset range of cells.
The cols parameter can either be a positive or negative number. If it is negative, then the offset column is to the left of the reference parameter; if it is positive, then the offset column is to the right of the reference parameter. This parameter is required.
[height]the height parameter is the number of rows high that the OFFSET function will return. The height parameter must be a positive number. The height parameter is optional; if it is omitted, the height parameter will be the same number of rows as the reference parameter.
[width]the width parameter is the number of columns wide that the OFFSET function will return. The width parameter must be a positive number. The width parameter is optional, and if omitted, the width parameter will be the same number of columns wide as the reference parameter.

The OFFSET function will return the #REF! error message if the offset cell reference isn’t a valid cell reference, for instance, if it is less than row 1 or column A.

If you’re not familiar with Excel Error Messages, check out my blog here.

OFFSET Example

For our OFFSET function example, we are going to use the OFFSET function to return a value from a list of fruit, a certain number of rows down from cell A1, by inputting a value into cell C2.

We will use the list of fruit we used in our previous example in cells A1:A10.

List of Fruit 2

In cell D2, we type the following formula:

=OFFSET(A1,C2,0,1,1)

Now, if we input the value 0 into cell C2, our formula in cell D2 returns the value Apple.

OFFSET Function Exampe Part 1

If we type a different number, for instance, the value 3 into cell C2, the formula returns the value Pineapple.

OFFSET Function Example

Okay, so what’s going on here? Let’s break this formula down:

  • reference – the reference parameter is the cell A1. This means that the cell reference or reference to a range of cells returned by the OFFSET function will be offset from cell A1.
  • rows – the rows parameter is the value that is input into cell C2. In our example, we input the value 3, which means the cell reference returned by the OFFSET function was offset by 3 rows down from the reference parameter, cell A1, which is row 0.
  • cols – the cols parameter is the value, 0. In our example, this means that the OFFSET function returned a cell reference from the same column as the reference parameter, cell A1, which is in column A.
  • height – the height parameter is the value, 1, which means that the reference returned by the OFFSET function was only one row high.
  • width – the width parameter is the value, 1, which means that the reference returned by the OFFSET function was only one column wide.

COUNT Syntax

The COUNT function counts the number of cells within a range of cells that contain numbers. You can provide the COUNT function up to 255 parameters containing cell references or cell ranges and it will count the total number of cells within the list of parameters that contain numbers.

For example, if you enter the following formula =COUNT(B1:B10) and 8 of the cells in the range of cells B1:B10 contain numbers, the formula will return 8.

The syntax of the COUNT function is COUNT(value1, [value2])

ParameterDescription
value1the value1 parameter is the first cell reference or range of cells that you want to evaluate and count the number of cells containing numbers. This parameter is required.
[value2]the value2 parameter is the second cell reference or range of cells that you want to evaluate and count the number of cells containing numbers. The COUNT function can have up to 255 parameters containing cell references or range of cells. This parameter is optional.

COUNT Example

In our COUNT function example, we are going to count how many cells in the range of cells A1:A10 contain numbers.

List of Numbers

To do this, we input the following formula into cell C2:

=COUNT(A1:A10)

Once we do this and press enter, the formula returns the value 8.

COUNT Function Example

This is because 8 of the 10 cells in the range of cells A1:A10 contain numbers. It is important to note here that even if these blank cells had contained any value other than numbers, the COUNT function would have still returned the value 8. This is because the COUNT function only counts numbers.

A simple breakdown of the formula is as follows:

  • value 1 – the value 1 parameter is the range of cells A1:A10 that will be evaluated and the cells containing numbers counted.

COUNTA Syntax

The COUNTA function counts the number of cells within a range of cells that aren’t blank. You can provide the COUNTA function up to 255 parameters containing cell references or cell ranges and it will count the total number of cells within the list of parameters that aren’t blank, i.e., cells that contain numbers, text, symbols, or functions even if the value returned by the function is (“”).

For example, if you enter the following formula: =COUNTA(B1:B10) and 8 of the cells in the range B1:B10 contain any sort of value, the COUNTA function will return 8.

The syntax of the COUNTA function is COUNTA(value1, [value2]).

ParameterDescription
value1the value1 parameter is the first cell reference or range of cells that you want to evaluate and count the number of cells that are non-blank. This parameter is required.
[value2]the value2 parameter is the second cell reference or range of cells that you want to evaluate and count the number of cells that are non-blank. The COUNTA function can have up to 255 parameters containing cell references or a range of cells. This parameter is optional.

COUNTA Example:

In our COUNTA function example, similar to the COUNT example, we are going to count how many cells in the range of cells A1:A10 contain text, numbers, or symbols.

List of Text, Numbers and Symbols

Like before, we insert a formula into cell C2: This time, the formula is:

=COUNTA(A1:A10)

Once we do this and press enter, the formula returns the value, 7.

COUNTA Function Example

This is because 7 of the 10 cells in the range of cells A1:A10 contain numbers, text, or symbols. It is important to note here that even if a blank cell contained a function that returned the value (“”), it would still count, even though the cell appears to be blank.

A simple breakdown of the formula is as follows:

  • value1 – the value1 parameter is the range of cells A1:A10 that will be evaluated and the number of cells containing text, numbers, and symbols counted.

Putting Things Together

Right. Let’s put it all together and make a Dynamic Named Range. In the following section, we are going to create two Dynamic Named Ranges; one made up purely of numbers, and the other made of text.

To do this, we are going to use the Name Manager dialogue (Windows) or Define Name dialogue (Mac). To get an in-depth description of how to use the Name Manager dialogue (Windows) or Define Name dialogue (Mac), check out my blog post by clicking here.

Dynamic Named Range of Numbers

In this example, we have a list of numbers in cells A1:A9, and when we type a number into cell C2, a formula in cell D2 returns the value in our list of numbers in the same row as the number we have entered in cell C2. Once we’ve done this, we add a new number to our list in cell A10, and without modifying any formulas, we will add the new number to our Named Range. To prove this, we will type the value 10 into cell C2, and the value in cell A10 will be displayed in cell D2.

To start, we need a list of numbers in cells A1:A9, as shown below.

List of Numbers 2

One important note here: Notice that we don’t have any cells in our list that don’t contain numbers. For this formula to work, we must only have a consecutive list of numbers in the column we use for the Dynamic Named Range.

Next, we open the Name Manager dialogue (Windows) or Define Name dialogue (Mac) on the Formula tab of the Ribbon, as shown below.

Name Manager Dialogue - Windows

Name Manager Dialogue – Windows

Define Name Dialogue - Mac

Define Name Dialogue – Mac

When you’ve done this, either the Define Name dialogue (Mac) or the Name Manager dialogue (Windows) will appear.

In Windows, click the New button, on the Mac, click the plus sign (+).

Create New Named Range - Windows

Create New Named Range – Windows

Create new Named Range - Mac

Create New Named Range – Windows

Then, on Windows, click in the Name: textbox, on the Mac click in the Enter a name for data range textbox. You can’t use spaces when naming Named Ranges. Type NumRange in the textbox, as shown in the image below.

Once this is done, click in the Refers to: textbox on Windows or Select range of cells: textbox on the Mac and type the following formula:

=OFFSET(‘Numbers Dynamic Range’!$A$1,0,0,COUNT(‘Numbers Dynamic Range’!$A:$A),1)

Once this is done, click the OK button, and you’re done.

Create NumRange Named Range Windows Part 2

Enter Name and Formula – Windows

Create NumRange - Mac

Enter Name and Formula – Mac

You’ve created the Dynamic Named Range NumRange.

Now, if we type the value, 7, into cell C2, the value, 84, is displayed in cell D2.

NumRange Example Part 1

The formula that returns the value in cell D2 isn’t important for this example, but if you are interested in how this is achieved, download the example spreadsheet that is available with this blog post by clicking here.

Okay, but what is the formula doing that we input earlier? The OFFSET function is returning a reference to a range of cells rather than just a single cell. The COUNT function counts the number of cells in column A that contain numbers and returns this value to the OFFSET function as the height parameter. The reference to the range of cells returned by the formula starts at the reference parameter value, cell A1, and continues down 9 rows because the height parameter is 9, making a cell range of A1:A9.

Now, if we add a new number to our list in cell A10, it is automatically added to our Dynamic Named Range.

To prove this, we can type 56 into cell A10.

List of Numbers 3

Then, in cell C2, we type 10 and press Enter.

You should now see the value, 56, in cell D2.

NumRange Example Part 2

What’s changed? The COUNT function automatically identifies that there are now 10 cells in column A containing numbers, and the OFFSET function now returns a reference to the range of cells A1:A10.

Let’s break the formula down in order to see what is happening.

  • (OFFSET) reference – the reference parameter is the cell, A1. This means that the cell reference or range of cells will be offset from cell A1. I suggest sticking to cell A1 when using this method and putting the first value of your list into cell A1. Otherwise, you need to adjust other parts of the formula to match this value.
  • (OFFSET) rows – the rows parameter is a value that is input into cell C2. In our example, we input the values 7 and 10, which means that the cell reference returned was offset by 7 rows down from the reference parameter, cell A1 the first time, then 10 rows the second time.
  • (OFFSET) cols – the cols parameter is the value 0. In our example, this meant that we returned a cell reference from the same column as the reference parameter, cell A1, which is in column A.
  • (OFFSET) height – the height parameter is the value return by the COUNT
    • (COUNT) value1 – The value1 parameter evaluates all the cells in column A using the following syntax $A:$A. If you want to reference an entire column, then delete the reference to any rows.
  • (OFFSET) width – the width parameter is the value 1, which means that the reference returned was only one column wide, column A.

Dynamic Named Range of Text

When creating a Dynamic Named Range for a list containing text, symbols, and numbers, the process is almost identical to the process for a list with just numbers, except we use the COUNTA function.

In this example, instead of a list of numbers in cells A1:A9, we use our list of fruit. Again, we input an index value in to cell C2 and a formula in cell D2 returns the value in our list of fruit at the same row as the number entered in cell, C2.

To start with, we need a list of fruit in cells A1:A9, as shown below.

List of Test

Similar to the COUNT function, we don’t have any cells in our list that that are blank; they should all have numbers, text, or symbols for the COUNTA function to return the correct value.

Next, we open the Name Manager dialogue (Windows) or Define Name dialogue (Mac) on the Formula tab of the Ribbon, as shown below.

Name Manager Dialogue - Windows

Name Manager Dialogue – Windows

 

Define Name Dialogue - Mac

Define Name Dialogue – Mac

When you’ve done this, either the Define Name dialogue (Mac) or the Name Manager dialogue (Windows) will appear.

In Windows, click the New button, on the Mac, click the plus sign (+).

image-23-create-textandnumrange-named-range-windows-part-1

Create New Named Range – Windows

Create New Named Range - Mac

Create New Named Range – Mac

Then, on Windows, click in the Name: textbox, on the Mac click in the Enter a name for data range textbox. You can’t use spaces when naming Named Ranges. Type Textand NumRange in the textbox.

Once this is done, click in the Refers to: textbox on Windows or Select range of cells: textbox on Mac and type the following formula:

=OFFSET(‘Text and Num Dynamic Range’!$A$1,0,0,COUNTA(‘Text and Num Dynamic Range’!$A:$A),1)

Once this is done, click the OK button, and you’re done.

Create TextAndNumRange Named Range - Windows Part 2

Enter Name and Formula – Windows

Create textAndNumRange Named Range - Mac Part 2

Enter Name and Formula – Mac

You’ve created the Dynamic Named Range TextAndNumRange. Please note that we used the name TextAndNumRange just to signify that the Named Range could contain both text and numbers when using the COUNTA function.

Now, if we type the value 4 into cell C2, the value Pineapple is displayed in cell D2.

TextAndNumRange Example Part 1

Again, the formula that returns the value in cell D2 isn’t important for this example, but if you are interested in how this is achieved, download the example spreadsheet that is used in this blog post by clicking here.

The formula works in exactly the same way as the previous example, except the COUNTA function counts the number of cells in column A that contain either text, numbers, or symbols and returns this to the OFFSET function as the height parameter.

If we add a new fruit to our list in cell A11, it is automatically added to our Dynamic Named Range.

To prove this, we can type Apricot into cell A10.

List of Text 2

Then, in cell C2, when we type 10 and press Enter.

You should now see in cell D2 the value Apricot.

TextAndNumberRange Example Part 2

Using a Dynamic Named Range with a Drop-down Menu

Dynamic Named Ranges can be really useful with drop-down menus, also referred to as drop-down lists. If you’re not sure what a drop-down list is, checkout my blog post here.

For our final example, we are going to create one final Dynamic Named Range of fruit, this time called the TextAndNumRange2. We follow the same steps as the previous examples; create a list of fruit in cells A1: A9 as shown below.

List of Text 3

When you’ve done this, either the Define Name dialogue (Mac) or the Name Manager dialogue (Windows) will appear.

In Windows, click the New button, on the Mac, click the plus sign (+).

Create TextAndNumRange2 Named Range Windows Part 1

Create New Named Range TextAndNumRange2 – Windows

Create TextAndNumRange2 Named Range Mac Part 1

Create New Named Range TextAndNumRange2 – Mac

Then, on Windows, click in the Name: textbox, on the Mac click in the Enter a name for data range textbox. You can’t use spaces when naming Named Ranges. Type Textand NumRange2 in the textbox.

Once this is done, click in the Refers to: textbox on Windows or Select range of cells: textbox on Mac and type the following formula:

=OFFSET(‘Drop-down Menu’!$A$1,0,0,COUNTA(‘Drop-down Menu’!$A:$A),1)

Once this is done, click the OK button;

Image 33 - Create TextAndNumRange2 Named Range - Windows Part 2

Enter Name and Forumla – Windows

Create TextAndNumRange2 Named Range - Windows Part 2

Enter Name and Formula – Mac

You’ve created the Dynamic Named Range TextAndNumRange2.

Now to create the drop-down menu. Click in cell, C2, then click the Data tab on the Ribbon, and then click the Data Validation button. The Data Validation dialogue should appear.

Data Validation Windows

Data Validation Dialogue – Windows

Data Validation - Mac

Data Validation Dialogue – Mac

Now, in the Allow: drop-down list, select List from the drop-down menu.

Image 35 - Select List Option Data Validation Dialogue

Select the In-cell dropdown option, as shown below, type =TextAndNumRange2 and click OK.

Enter Named Range On Data validation dialogue

Next to cell C2, you should see a little icon that looks like an arrow pointing downward.

TextAndNumRange2

When you click the icon, you will see the fruit on our list appear in a drop-down menu. Simply click the fruit you want to select in order to enter it into the cell.

If we add a new fruit to our list; for example, typing Blueberry into cell A10, it will instantly be added to our drop-down menu.

TextAndNumRange2 Add Blueberry Dropdown Menu

If you’re not familiar with drop-down menus or would like to learn more about data validation options, check out my blog post here.

Wrap-up

That’s it for this blog post. I hope you found it useful and have seen how useful Dynamic Named Ranges can be when you have lists of values that need to be regularly updated. In this post, you’ve learned how to create and use a Dynamic Named Range. You’ve also seen how to use a Dynamic Named Range with a drop-down menu. We’ve taken a look at the OFFSET, COUNT, and COUNTA functions, and how they can be used to create a Dynamic Named Range.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit