In Excel, named ranges are a convenient way to reference a continous range of cells using a descriptive name instead of a complicated expression or formula. However, when the referenced range is not static, and the number of rows and/or columns is likely to change between updates of the dataset in question, a simple range reference will only be useful for as long as the dataset fits the range’s dimensions at that time. As additional rows or columns are added, the named range no longer sufficiently references the dimensional span of the dataset.

However, dynamic datasets can be referenced with a named range. The key is to avoid explicit cell references in the definition.

Suppose that our dynamic dataset will reside in Sheet1, and the first cell, regardless of the ultimate depth and breadth of a particular iteration of the dataset , will be A1. Also, at any given time, the number of columns and rows in the dataset will be greater than or equal to 1.

To declare dynamic range reference, enter Ctrl + F3 to open the Name Manager, and select New... in the upper left-hand side. Specify a name for the range in question, and a description in the Comment: section (if desired). In the Refers to: section, enter the following:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Note that OFFSET has the following declaration:

OFFSET(reference, rows, cols, [height], [width])
  • reference is the reference from which you want to base the offset. It must refer to a cell or range of adjacent cells; otherwise, OFFSET returns #VALUE!.

  • rows is the number of rows, up or down, that you want the upper-left cell to refer to.

  • cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to.

  • height is the height, in number of rows, that you want the returned reference to be.

  • width is the width, in number of columns, that you want the returned reference to be.

We set reference to $A$1, and rows and cols to 0, since the named range refers to $A$1 as the static point. COUNTA($A:$A) returns the number of consecutively populated cells in column A starting from $A$1 and working down; COUNTA(\(1:\)1) returns the number of consecutively populated cells in row 1 starting from $A$1 and working right. Upon return, the call to OFFSET will yield the dimensions of the continuous range of cells starting in cell $A$1, whatever those dimensions may be. With this formulation, you’ll be certain that when you call a named range, what’s actually being referenced and what you’re expecting to be referred will be one in the same.

Alternate References

There may be times when only one dimension of a dataset will be expected to vary between updates: Either the number of columns or the number of rows, but not both. In that case, the named range definition is a simplified version of the 2-varying dimensions named range.

To facilitate a varying number of columns between updates, specify the named range as:

=OFFSET($A$1,0,0,1,COUNTA($1:$1))

To facilitate a varying number of rows between updates, specify the named range as:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)