Author: Oscar Cronquist Article last updated on February 24, 2018

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves you time as you no longer need to adjust the cell reference in a formula.

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named range.

This makes the named range dynamic meaning you donยดt need to adjust cell references every time you add a new row or column to the list.

The formula takes care of only one list per sheet.

How to create a named range

1. Click "Formulas" tab on the ribbon.
2. Click "Name Manager".
3. Create a new named range and name it.
4. Type the formula below in "Refers to:" window:
5. Click close button.

Named range forumla:

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, COUNTA(Sheet1!\$A:\$A), COUNTA(Sheet1!\$1:\$1))

Explaining formula

Step 1 - Count the number of cells in column A that are not empty

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, COUNTA(Sheet1!\$A:\$A), COUNTA(Sheet1!\$1:\$1))

becomes

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, 4, COUNTA(Sheet1!\$1:\$1))

Step 2 - Count the number of cells in row 1 that are not empty

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, COUNTA(Sheet1!\$A:\$A), COUNTA(Sheet1!\$1:\$1))

becomes

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, 4, 3)

Step 3 - Return a reference of the cell at the intersection of a particular row and column

=Sheet1!\$A\$1:INDEX(Sheet1!\$1:\$65535, COUNTA(Sheet1!\$A:\$A), COUNTA(Sheet1!\$1:\$1))

becomes

=Sheet1!\$A\$1:\$C\$4

This cell reference changes whenever new rows or columns are added or removed.

Functions in this article:

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range