Dynamic Named Range in Excel

Dynamic Named Range in Excel

Sometime, the actual length of range is undetermined and hence it becomes important to set a range that can handle this undetermined state.

To have a better understanding of this concept, follow the steps below:

1. Further to the previous example of using Names range, where we had assigned a Name to a fixed range of A1 to A4 except that the Sum formula is shifted from Cell A5 to Cell B5

A1 to A4

2. In Formula tab and under the Defined Name group, click on the Name Manager

Name Manager

3. On Name Manager window, select the defined Name and click Edit

click Edit

4. In Edit Name window, set the range from $A:$A instead of $A$1:$A$4 and click OK to close the followed by clicking the Close button to close the Name Manager window.

click OK

5. Now, add a value in Cell A5 and you will observe that Sum value in Cell B5 is automatically updated which, previously, wouldn’t update because the defined range was absolute and fixed to Cell A1 to A4 whereas now, it is fixed to the whole Column A

Cell A1 to A4