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: 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 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:
Dynamic named range with OFFSET Dynamic named range with INDEX
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:
23 things to know about Excel Tables An introduction to Excel Tables and structured references (video) How to query a table with formulas (video)
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
