Dynamic Named Range in Excel
A dynamic named range in Excel increases in size on its own whenever you enter more data.
1. For example, highlight the cells from A1 to A4 and assign the name Prices to that range.
2. Calculate the sum.
3. If you add a value to the range, the total sum in Excel will not update by itself.
To make the named range grow automatically when you add new values, follow these steps.
4. Click Name Manager in the Defined Names group on the Formulas tab.
5. Click Edit.
6. Place the cursor in the “Refers to” box and write the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
Explanation: The OFFSET function requires five inputs: the starting cell $A$1, rows to shift 0, columns to shift 0, and the height of the range. The formula COUNTA($A:$A) counts all the cells in column A that contain any value. When you enter a value in the range, COUNTA($A:$A) goes up, and the OFFSET function returns a larger range.
7. Click OK and Close.
8. Now, Excel updates the sum automatically whenever you add a value to the range.
9/11 Completed! Learn more about formulas & functions ➝
Next Chapter: Keyboard Shortcuts