A dynamic named range in Excel automatically adjusts the range of cells it refers to as data is added or removed. This is especially useful when you are working with a table or list of data that changes frequently.

1. For example, select the range A1:A4 and name it Prices.

2. Calculate the sum.

3. When you add a value to the range, Excel does not update the sum.

To expand the named range automatically when you add a value to the range, execute the following the following steps.

4. On the Formulas tab, in the Defined Names group, click Name Manager.

5. Click Edit.

6. Click in the “Refers to” box and enter the formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Explanation: the OFFSET function takes 5 arguments. Reference: $A$1, rows to offset: 0, columns to offset: 0, height: COUNTA($A:$A) and width: 1. COUNTA($A:$A) counts the number of values in column A that are not empty. When you add a value to the range, COUNTA($A:$A) increases. As a result, the range returned by the OFFSET function expands.

7. Click OK and Close.

8. Now, when you add a value to the range, Excel updates the sum automatically.

Additional Notes in Dynamic Named Range:

• OFFSET can create a range that grows or shrinks based on the number of entries, making it ideal for dynamic data ranges.
• COUNTA ensures that the range dynamically adjusts to the number of entries, even when there are blank cells.
• You can apply the dynamic range to charts as well. When data is added, the chart automatically updates to reflect the new data.

This technique is incredibly useful for maintaining up-to-date data ranges without manually adjusting the range each time you add or remove data.