Dynamic Named Range in Excel

Add to Favorites
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.


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

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
Tutorial Details
Excel Function: COUNTA(), OFFSET()
Downloadable Files: Excel File
Similar Content
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...
Excel Forum