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

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

3. On Name Manager window, select the defined Name and 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.

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