Create Dynamic Named Range in Excel

When you are unsure about how long the named range should be or you will potentially add values to the range, the dynamic named range will expand automatically when new values are added. To create a dynamic named range, follow these steps.

1. Select the cell range D4:D9 and, in the name bar, name the range as “cost”.

2. In cell E4, calculate the sum of all values in D4 to D9 by typing in “=SUM(cost).

3. Now, let’s try to add 89 in cell D10. The sum in cell E4 does not update, suggesting that our named range “cost” is not a dynamic one.

Let’s make a dynamic named range by adding a function to it.

4. Click “Name Manager” on the Formulas tab.

5. Select your named range and then click Edit.

6. In the Refers to, box, rewrite the formula and incorporate OFFSET function. The formula is =OFFSET(Sheet3!$D$1,0,0,COUNTA(Sheet3!$D:$D),1)

Note: the OFFSET function has five arguments. (a) reference $D$1, (b) rows to offset 0, (c) columns to offset 0, (d) COUNTA ($A:$A) counts the number of values in column D that are not empty, and (e) width is 1. This OFFSET function is going to expand the range upon entering a new value outside the range.

7. Click OK and then click Close.

8. Now add value in cell D10, Excel will update the sum in cell E4, meaning that the named range “cost” has expanded to include cell D10 into the range.

9 of 10 finished! Recommending more on Formulas and Functions: Next Example >>

<< Previous Example | Skip to Next Chapter 07: Find and Select