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],…)…

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar