A dynamic named range, also called simply a "dynamic range" is a specially constructed range that expands automatically to accommodate new data. In the example shown, we have a small set of data in B5:B13, and two formulas calculating a minimum and maximum value like this:
=MAX(data)
=MIN(data)
Where "data" is a dynamic named range corresponding to B5:B13. The formula used to create this dynamic named range is based on the OFFSET function:
=OFFSET(B5,0,0,COUNTA(B5:B100))
See detailed explanation here.
The primary advantage of a dynamic named range is that the reference responds to changes. If values are removed from the bottom of the list, the range contracts. If values are added to the bottom of the list, the range expands. This minimizes the number of cells Excel needs to calculate and provides an easy-to-use reference that targets only data of interest.
Create a dynamic named range with a formula
There are two main ways to create a dynamic named range. The first way is to use a formula based on either the OFFSET or INDEX functions. The links below provide details on how to do this:
Using a formula to set up a dynamic named range is a traditional approach, and gives you exactly the range you want without any overhead. However, formulas that define dynamic named ranges can be difficult to understand.
Create a dynamic named range with an Excel Table
The other way to create a dynamic named range is to use an Excel Table. Excel Tables automatically change to fit data and provide a formula syntax called "structured references" that can be used to target table elements by name. See the links below for more information:
