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**.

Now, type **Orange** in cell **C2**.

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**.

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

You should now see the value **10** in cell **D2**.

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])**

Parameter | Description | |
---|---|---|

reference | the 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. | |

rows | the 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. | |

cols | the 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**.

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**.

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

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])**

Parameter | Description |
---|---|

value1 | the 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.

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**.

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]).**

Parameter | Description |
---|---|

value1 | the 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.

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**.

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.

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.

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 (**+**).

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.

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**.

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**.

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

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

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.

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.

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 (**+**).

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.

You’ve created the Dynamic Named Range **TextAndNum****Range**. 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**.

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**.

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

You should now see in cell **D2** the value **Apricot**.

# 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.

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 (**+**).

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;

You’ve created the Dynamic Named Range **TextAndNumRange2**.

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

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

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

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

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.

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.