Free Online Tutorials on Spreadsheets

Dynamic Named Range in Excel

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.

Excel starts to update the sum.

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.

Copyright © 2016 - 2019 | All Rights Reserved