# Dynamic Named Range in Excel

How to create a named range that expands automatically when a new value is added to the range.

This is a great little trick and it will save you a lot of time from updating your named ranges in Excel.

## The Problem

When you create a named range in Excel, it will not update when you add a new value to that range or list.

## The Solution

The solution is to use the OFFSET() function in a named range. This is what will allow a range to automatically update when a new value is added to it.

Here is our sample list:

Before we go to the Name Manager screen to enter the formula and create the Named Range, I find it easiest to create the formula in the worksheet since it will help correct any errors we may have.

For this list we want to use this formula:

```
```=OFFSET($A$2,0,0,COUNTA($A:$A),1)

You can see how putting this in the worksheet allows you to see where everything is pointing and to better troubleshoot any potential errors.

There are a few things that you need to change to make this work for you.

First, where it says **$A$2** in the formula above, you need to change that to the cell reference for the first cell in the list. So, if the first cell was C1, you would use this: **$C$1**

Then just change **$A:$A** to the column where your list is located. So, if it was in column D, you would use this: **$D:$D**

Now that we have the formula figured out, let's put it in a named range and test it.

Go to the Formulas tab and click **Name Manager**:

You will see this screen:

Click the button **New...**

Change the **Name**, and **Refers to** section (the others are optional):

For the **Refers to** section I just copy/pasted the formula that we created in the worksheet into there to avoid any potential errors.

Going back to the worksheet, now we can use the name like this:

Here is the result:

Now, if I add more numbers to the list:

You can see that the formula was automatically updated to include the new number in the calculations.

This is all you need to do to create a dynamically updating named range in Excel!

## Formulas Explained

In case you were curious, I will now explain the two functions used to create this dynamic named range.

The first function is the **OFFSET() **function. This allows you to reference a range that is a certain distance or size from a reference cell, which, as used above, is the first cell in the list.

This is the function that allows you to create a variable sized range.

The **COUNTA()** function counts how many cells in a selection are not empty. This finds out how many cells contain something, in column A in this example, and then puts its result into the OFFSET() function since it is nested within the OFFSET() function.

If you want to limit the COUNTA() function to a range that is not the entire column, just put the desired range reference in there and make sure to make that reference absolute like this: **$A$1:$A$100** or something similar (the dollar signs make it absolute which means the cell reference won't change).

## Things to Note

In my example, since I have a header in Cell A1, this formula will actually return a range that is one larger than the actual range of data. In this case, that does not matter. However, if I wanted to make the dynamic named range exactly fit my data, I would add a **-1 **after the COUNTA() function (there are other ways to do this but this is the simplest in this example).

Here is the current formula with the extra cell:

Here is the updated formula:

```
```=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

Note the COUNTA function now looks like this **COUNTA($A:$A)-1** with the **-1** after it.

Here you can see it working correctly in the spreadsheet:

As I said above, the first formula will work in most instances and always if you don't have a header, but this new one will always work, even when you have a header for your data.

## Question? Ask it in our Excel Forum

Tutorial: I'll show you how to make formulas and functions that automatically update when more data...

Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...

Tutorial: How to count the number of errors in a range in Excel; also, how to count the occurrence ...

Tutorial: How to quickly delete all empty cells or rows from a range in Excel. This allows you to q...

Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...

Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...