Categories
Excel

Dynamic Named Ranges

Dynamic range is a way to create a named range that can automatically expand and contract to the size of your data. It is useful when you want to use the range in formulas, charts, or PivotTables without having to update it manually every time you add or delete data.

To understand how dynamic named ranges work, you should familiarize yourself with Excel’s OFFSET and COUNTA functions:

  • The OFFSET function returns a reference to a range that is offset from a starting cell by a certain number of rows and columns.
  • The COUNTA function counts the number of non-blank cells in a column or row.

OFFSET Function

The syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])
  • reference is the starting point, which can be a single cell or a range of cells.
  • rows is the number of rows to move up or down from the reference. A positive value means moving down, and a negative value means moving up.
  • cols is the number of columns to move left or right from the reference. A positive value means moving right, and a negative value means moving left.
  • height (optional) is the height of the returned range in rows. If omitted, it defaults to the height of the reference.
  • width (optional) is the width of the returned range in columns. If omitted, it defaults to the width of the reference.
OFFSET Function Examples

COUNTA Function

The COUNTA function (stands for “Count All”) is used to count the number of non-blank cells in a range or a cell reference. The syntax of the COUNTA function is:

=COUNTA(value1, [value2], ...)
  • value1 is the first argument, which is the first cell or range you want to count.
  • [value2], ... (optional) you can include multiple cells or ranges to count them as well.

Here are some examples:

  • =COUNTA(A1:A10) counts how many cells in the range A1 to A10 contain data.
  • =COUNTA(A1:A10, B1:B10, C1:C10) counts the number of non-empty cells in multiple ranges.
  • =COUNTA(1:1) counts the non-empty cells in the entire row 1.
  • =COUNTA(A:A) counts the non-empty cells in the entire column A.

Creating Dynamic Ranges

We’ll start off with the simplest of dynamic named ranges, one that will expand down a single column, but only as far as there are entries in that column. For example, if column A contains 10 continuous rows of data, your dynamic named range will incorporate the range A1:A10. Follow these steps to create a basic dynamic named range:

  1. Go to Formulas tab and click Define Name icon.
  2. Type MyRange in the Name box in the “New Name” dialog box.
  3. In the Refers To box, type the following:
=OFFSET($A$1,0,0,COUNTA($A:$A),1)

Click OK.

Note: Dynamic named ranges are not available via the standard Name box, immediately to the left of the Formula bar.

The dynamic named range you created in the previous example nests the COUNTA function as the Height argument in the OFFSET function. The OFFSET function defines the range starting from cell A1 and extending to the last non-empty cell in column A.

Note: When defining the range for COUNTA, resist the temptation to include an entire column of data so that you do not force the COUNTA function to count potentially thousands of unnecessary cells.

If you have a list that contains numeric data only, and at the end of this list you want to store text but don’t want this text included as part of your dynamic named range, you could replace the COUNTA function with Excel’s standard COUNT function. COUNT counts only cells containing numeric data.

In the next example, you will use the dynamic named range to define a table of data that you want to be dynamic. To do this, type the following function into the Refers To: box in New Name dialog box: 

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

Here, the dynamic named range will expand down as many entries as there are in column A, and across as many rows as there are headings in row 1. If you are sure the number of columns for your table of data will remain stable, you can replace the second COUNTA function with a fixed number such as 10.

Dynamic Named Ranges Examples

The following list outlines other types of dynamic named ranges you might find useful. For all of these examples, you will need to fill column A with a mix of text and numeric entries.

To do this, select Formulas » Define Name, and in the Name: box, type the range name (for instance, MyRange). The only part that will change is the formula you place in the Refers To: box:

Expand the dynamic range down as many rows as there are numeric entries:

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

Expand down as many rows as there are numeric and text entries:

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

Expand down to the last numeric entry:

=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A))

If you expect a number larger than 1E+306 (a 1 with 306 zeros), change this to a larger number.

Expand down to the last text entry:

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))

Expand down based on another cell value. Enter the number 10 in cell B1, and then, in the Refers To: box, type the following:

 =OFFSET($A$1,0,0,$B$1,1)

Now change the number in cell B1, and the range will change accordingly.

Expand down one row each month:

=OFFSET($A$1,0,0,MONTH(TODAY( )),1)

Expand down one row each week:

=OFFSET($A$1,0,0,WEEKNUM(TODAY( )),1)