Dynamic Named Range in Excel

A dynamic named range expands automatically when you add values to the range. To better understand this concept let’s take a look at the below example implementation.

Sample Implementation:

Step 1: Select the range A1:A5 and give the name like prices. After that calculate the sum of the range as shown in the below image:

Step 2: Then we will add a number after the A5 range, Excel does not calculate the updated value, which we have entered.

Now the calculate the sum after the A5 range we have to do the following steps for expanding the named range automatically.

Step 3: Go to the Formulas tab > Defined Names group > Name Manager > Click Name Manager.

Step 4: Click on Edit.

Step 5: After the click Edit we have to enter this OFFSET($A$1,0,0, COUNTA($A:$A),1) Bold formula in the reference to section then click on the ok button.

Step 6: After clicking OK and then Close.

Step 7: Now, whenever you have entered the value one after another the excel automatically counts the sum automatically. For reference see the below image:

That’s it. We have successfully used the dynamic named range feature of excel.