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)

When working with Excel formulas, a common term is “empty string”. A text string is a text value like “apple”. An empty string is a text value that has no value. Since text in Excel formulas must be enclosed in double quotes, an empty string appears as empty double quotes ("").

For example, in the screenshot shown, the formula D5, copied down, is:

=IF(C5>10,"x","")

Here, we use the IF function to check the value in column C5. If the value is greater than 10, the formula returns “x” as text. Otherwise, IF returns an empty string ("").

When an empty string is the result of a formula, it looks like the cell is blank. So, empty strings are commonly used to return a value that looks like nothing. In other words, return a result that appears as an empty cell.

One thing to note is that some functions will treat empty strings as if they do have a value. For example, the ISBLANK function will return FALSE if used on a cell that contains a formula that has returned “”. Likewise, the COUNTA function will include cells that contain formulas that have returned “”.