Dynamic Named Range in Excel

Add to Favorites

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:


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.

Question? Ask it in our Excel Forum

Excel Function: COUNTA(), OFFSET()
Downloadable Files: Excel File

Similar Content on TeachExcel
Dynamic Formulas that Update When you Add Data in Excel
Tutorial: I'll show you how to make formulas and functions that automatically update when more data...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to ...
Count the Errors in a Range in Excel
Tutorial: How to count the number of errors in a range in Excel; also, how to count the occurrence ...
Formula to Count Occurrences of a Word in a Cell or Range in Excel
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...