Dynamic Named Range in Excel

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:

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:

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.