A dynamic named range in Excel expands automatically when new values are added to the range.
For Example, select the range A1:A6, name it ‘Data’ and then calculate the sum.
When new values add to the range, Excel does not update the sum.
To make the range dynamic, execute following steps:
1- In the Formulas tab, go to the Defined Names section and click the Name Manager. (Shortcut key alert: the Name Manager also open by pressing Ctrl+F3).
2- Click ‘Edit‘ and in the ‘Refers to‘ box type the formula ‘=OFFSET($A$1,0,0,COUNTA($A:$A),1)’. Press Enter.
In the above-mentioned formula, the syntax of the OFFSET function is:
=OFFSET(reference, rows, cols, [height], [width])
The function has following arguments:
reference – Required. The reference from which you want to base the offset. In our example it is column ($A$1), the dollar sign ($) is used to make the reference absolute.
rows – Required. The number of rows (up or down) below the starting reference. In our example, we took 0.
cols – Required. The number of columns (right) to the starting point. i.e. 0 in the example.
[height] – Optional. The height in the number of rows of the returns reference. For this COUNTA function is used in the example to know the number of values in column A that are not empty.
[width] – Optional. The width in columns of the returned reference. i.e. 1.
Once you finalize the formula, whenever you add value to the range, COUNTA($A:$A) increases and the range returned by the OFFSET function expands.