Named ranges are super cool.
Wouldn’t it be even better if we wouldn’t have to keep re-adjusting them? Imagine if they would expand and contract according to what is inside of them?
It is possible, with a couple of clever formulas.
Here I have to credit to an unknown author.
I didn’t come up with this option. I am nowhere near as clever.
Nobody knows who invented this technique in excel, but perhaps it is not the most important thing here.
The most important thing is to spread this technique to as wide of an audience as I can.
If you are here I presume that you understand the basics of Named ranges.
So I will not bore you with this.
For the coming exercise we will add the formulas for the named ranges in the Name Manager under the Formulas tab.
Expanding and contracting named ranges
All of the examples presume that the cells you trying to add to a dynamic range are in Column B.
For only numeric values in the cells use:
For mixed (numeric and text) values in the cells use:
Numeric and Text
Hope that you can use this technique in your day to day work.