Dynamic Named Range in Excel

Author:

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

Excel Function: COUNTA(), OFFSET()

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...
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 ...
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 ...
Generate Random Numbers within a Range in Excel
Tutorial: How to generate random whole numbers (integers) that are between two numbers.  This allow...
Tutorial Details
Excel Function: COUNTA(), OFFSET()