Dynamic Range Name

What is a dynamic range?

By Neale Blackwood

A dynamic range is a range whose size changes as data is added or deleted in an area. You can use this technique to define a range for a Data Validation drop down list or define a variable length data range for a pivot table.

To create a dynamic range, follow these steps. (You can enter all the formula in lowercase. Excel automatically changes the functions and references to uppercase)

  1. Open a new workbook. (Ctrl + n)
  2. Type the numbers 1 in A1, 2 in A2 and 3 in A3.
  3. Hold Ctrl and press F3
  4. In the Names in Workbook: box type Test
  5. Delete whatever is in the Refers To:box and type =offset($a$1,0,0,counta($a:$a))
  6. Click the Add button, then click OK

Now that you have created a dynamic range you can use it as a Data Validation List.

  1. Click cell B1
  2. Click the Data menu, click Validation
  3. Click the drop down arrow in the Allow: box and select List
  4. Click in the Source: box and press F3. Double-click Test
  5. Click OK

Click in cell B1 and the click the drop down arrow, you will see the numbers 1,2 3 listed. If you type a number in cell A4 and then click the B1 drop down list you will see the new number has been added to the list. Deleting the last number(s) will also adjust the list. This formula looks at the whole of column A. If the range is only in the first 100 rows of column A, you could use the following formula:

=offset($a$1,0,0,counta($a$1:$a$100))

Anything below row 100 would be excluded from the range.

The above examples are single column ranges. You can also define a multi-column range for use as a pivot table data range. The formula below will create a three column range based on the contents of column A.

=offset($a$1,0,0,counta($a$1:$a$100),3)

The 3 at the end of the OFFSET formula specifies how many columns. The default value is 1.

Note the above techniques only work if there are no empty cells between the entries in the range. To create a single column dynamic range that includes empty cells within a range in column A, use one of the following formulae.

For text ranges use:

=offset($a$1,0,0, match(“*”,$a:$a$,-1))

For numeric ranges use:

=offset($a$1,0,0,match(1e+100,$a:$a))