Create a Dynamic Named Range

A dynamic named range is a named range that automatically adjusts to include new data. This means that you can add data to your range without having to manually change the formula that defines it.

One method that seems popular for creating a dynamic named range uses OFFSET and COUNTA to define the range. However, OFFSET is a volatile function. And, in addition, COUNTA doesn't allow the range to contain empty cells. Fortunately, INDEX and MATCH can be used instead.

Volatile functions re-calculate each time a change of data occurs in any cell on any worksheet, regardless of whether a cell they are referencing has changed. As a result, re-calculation times can be prolonged.

Note, unlike a named range, a dynamic named range will not appear in the Name box drop down list. However, you can type the name in the Name box and press ENTER to select the range on the worksheet.

Using OFFSET and COUNTA

Create a Dynamic Named Range - Using OFFSET and COUNTA

To create a dynamic named range for Column B, starting at B2...

  1. Select 'Formulas > Defined Names > Defined Name' or  'Insert > Name > Define'
  2. Define the dynamic range as follows...
    • Name: Sales
    • Refers to: =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)
  3. Click OK

Sample Workbook: Download

Using INDEX and MATCH

Dynamic Named Range - Data Containing Text Values

To create a dynamic named range for Column B, starting at B2...

  1. Select 'Formulas > Defined Names > Defined Name' or  'Insert > Name > Define'
  2. Define the dynamic range as follows...
    • Name: Sales
    • Refers to: =$B$2:INDEX($B:$B,MATCH(9.99999999999999E+307,$B:$B,1))
  3. Click OK

Note that if the data contains text values instead of numerical values, you'll need to replace 9.99999999999999E+307 with REPT("z",255).

Sample Workbook: Download