A dynamic named range in Excel automatically expands when new data is added, ensuring that formulas always include the updated dataset without manual adjustments. Using functions like OFFSET along with Name Manager, the range can be made flexible so calculations such as SUM update automatically as the data grows.
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: When a new value is added below the range A1:A5, the existing SUM does not update automatically because the named range still refers only to the original cells.
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 clicking Edit, enter the following OFFSET($A$1,0,0, COUNTA($A:$A),1) formula in the reference field and click OK.
Step 6: After clicking OK and then Close.
Step 7: Now, whenever new values are entered one after another, Excel automatically updates the sum.
That's it. We have successfully used the dynamic named range feature of excel.