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.

• Creating Dynamic Named Range with OFFSET Function
• Creating Dynamic Named Range with INDEX Function
• Creating Dynamic Named Range by Creating a Table

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.

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

#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.

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

#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)

#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.

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

#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.

#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.

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