How to Create Dynamic Named Range in Excel

This post will guide you how to create dynamic named range that can expand automatically when you add a new value into the current range in Excel 2013/2016. How do I create a dynamic named range with a formula in Excel. How to create a dynamic named range by creating a table in Excel.

Assuming that you have created a static named range called “test” in range B1:B4, and you want to insert one cell into this named range and let it expands automatically into this test named range. How to do it. You can refer to the following three approaches to achieve the result.

Creating Dynamic Named Range with OFFSET Function


To create a dynamic named range in your worksheet, you can use a formula based on the OFFSET function in combination with the COUNTA function. Just do the following steps:

#1 select the range B1:B5, go to HOME tab in Excel Ribbon, and click Name Manager command under Defined Names group. And the Name Manager dialog will open.

create dynamic named range1

#2 click New button in the Name Manager dialog box, and the New Name dialog will open.

create dynamic named range2

#3 enter one name into Name text box (such as: test) in the New Name dialog box, click Ok button. Then back to Name Manager dialog box, click Close button.

create dynamic named range3

#4 you would notice that one named range called “test” has been created based on the selected range.

create dynamic named range4

#5 in step 1-4, you have created one static named range, and if you want to change it as dynamic named range, you need to click Name Manager command again. Select test name range in the Name Manager dialog box, and click Edit button, then enter the following formula in the Refers to text box. Click OK button.

=OFFSET($B$1,0,0,COUNTA($B:$B),1)

create dynamic named range5

#6 you can try to add a value to test name range, and it would be expanded automatically into test name range.

Creating Dynamic Named Range with INDEX Function


You can also use another formula based on the INDEX function and the COUNTA function in the Refers to text box to create a dynamic named range in Excel.  Like this:

=$B$1:INDEX($B:$B,COUNTA($B:$B))

When you add a new value in one existing name range in column B, the name range will expand automatically.

Creating Dynamic Named Range by Creating a Table


There is another method to create a dynamic named range in Excel, you can create a table based on the existing name range. Then that name range will be changed as dynamic named range. Just do the following steps:

#1 assuming that you have defined a name range as “text ” in range B1:B5. Select all cells in this name range.

create dynamic named range6

#2 go to INSERT tab, click Table command under Tables group. And the Create Table dialog will appear.

create dynamic named range7

#3 check or uncheck My table has headers in the Create Table dialog box, if your data have header, then check it, or uncheck it. Click OK button.

create dynamic named range8

#4 you can add one value after the data in test name range, new cell should be added into table and also be expanded into name range.

create dynamic named range9

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
Related Posts
How to Average Last N Values in Multiple Columns
Average Last N Values in Multiple Columns 14

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. In ...

How to Average the Last N Values in Excel
How to Average the Last N Values 10

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. We can apply it to calculate the average of numbers from a given range reference. In daily work, we may apply ...

How to Generate Random Values by a List in Different Cases in Excel
How to Generate Random Values in Excel10

Sometimes we may create some fake data for testing or some other purpose. If we want to create an amount of numbers, we may feel it is annoying by entering random numbers one by one. For this instance, we can ...

How to Find the First or Last Positive or Negative Number in a Column/List in Excel
How to Find the First or Last Positive or Negative Number in a Column9

Suppose we have a list of data entry, we want to look up the first positive number among them, is there any way to find it out? And if we want to look up the first or last negative number, ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Average Absolute Values in Excel
How to Average Absolute Values in Excel5

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine ...

How to Look Up the Lowest Value in A List by VLOOKUP/INDEX/MATCH Functions in Excel
Look Up the Lowest Value in A List by VLOOKUPINDEXMATCH 6

VLOOKUP function is very useful in our daily work and we can use it to look up match value in a range, then get proper returned value (the returned value may be just adjacent to the match value). Sometimes we ...

How to Dynamically Extract Unique Values from A Column List in Excel
Dynamically Extract Unique Values8

Suppose we have a list of some objects in one column, and some of them are duplicate, they also can be replaced by typing different object name, here’s the question, how can we dynamically extract unique values from this list ...

How to Find the Earliest and Latest Date in Excel
Find the Earliest and Latest Date 10

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function ...

How to Create Dynamic Drop Down List without Blank in Excel
create dynamic drop down list with blank5

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

Sidebar