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:
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 ands 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 up a dynamic named range is to use an Excel Table. Excel Tables automatically change to fit data, and provided a formula syntax called structured references that can be used to target table elements by name. See the links below for more information:
A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box . In the example, the formula in F6 is: = MAX ( sales ) where "...
A structured reference is a special syntax for referencing Excel Tables . Structured references work like regular cell references in formulas, but they are easier to read and understand. Structured references are also dynamic, and adjust...
An expanding reference (or expanding range) in Excel defines a range that expands as a formula is copied down or across cells. This is done by "mixing" absolute and relative references – making the first cell an absolute reference and the last cell...