Dynamic Excel refers to versions of Excel that offer Dynamic Array Formulas , currently Excel 365 and Excel 2021. We use the term Legacy Excel to refer to all other (non-dynamic) versions.
In fall 2018, Microsoft announced new functionality in Excel called “dynamic array formulas”. Dynamic array formulas have the ability to natively work with multiple values and can “spill” these results onto the worksheet.
The new functionality is provided in two parts:
- New functions designed to leverage dynamic array behavior directly
- New formula engine that lets older formulas and functions use dynamic arrays.
This is a big upgrade and a radical change to the way Excel behaves in some circumstances, and it is only available in Excel 365 and Excel 2021 for now. This means all other versions of Excel, including 2016 and 2019, do not have dynamic arrays.
For a quick summary of dynamic arrays, see: Dynamic Array
For details and examples, see: Dynamic Array Formulas in Excel
Dynamic Arrays are only available in Office 365 .
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:
- 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 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:
- 23 things to know about Excel Tables
- An introduction to Excel Tables and structured references (video)
- How to query a table with formulas (video)
