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`

.

## 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:

- Go to
`Formulas`

tab and click`Define Name`

icon. - Type
`MyRange`

in the Name box in the “New Name” dialog box. - 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)`