Author: Oscar Cronquist Article last updated on March 14, 2022

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.

1. Create a dynamic named range

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.

Back to top

1.1 How to create a named range

how to create a named range

  1. Press with left mouse button on the "Formulas" tab on the ribbon.
  2. Press with left mouse button on "Name Manager" button, a dialog box "Name Manager" appears.
  3. Press with left mouse button on "New..." button to create a new named range and name it.
  4. Type the formula below in "Refers to:" window:
    new named range
  5. Press with left mouse button on the "Close" button.

Back to top

1.2 Named range formula

create a dynamic named range 1

=$A$1:INDEX($1:$1048576, COUNTA($A:$A), COUNT($1:$1))

Back to top

1.3 Explaining formula

Step 1 - Count non-empty cells in column A

The COUNTA function counts non-empty cells in a given cell range or array.

COUNTA(value1, [value2], ...)

COUNTA($A:$A)

returns 3. There are three values in column A.

Step 2 - Count non-empty cells in row 1

COUNTA($1:$1)

returns 4. There are four values in row 1.

Step 3 - Create cell reference to last non-empty cell

The INDEX function returns a value based on a row and column number, however, it can also be used to create a cell reference.

INDEX(array, [row_num], [column_num], [area_num])

INDEX($1:$1048576, COUNTA($A:$A), COUNT($1:$1))

becomes

INDEX($1:$1048576, 3, 4)

and returns D3.

Step 4 - Create cell reference to the entire cell range

The colon character lets you combine two cell references and create a larger cell ref to a cell range.

$A$1:INDEX($1:$1048576, COUNTA($A:$A), COUNT($1:$1))

and returns $A$1:$D$3.

create a dynamic named range1

Back to top

1.4 When to use named ranges?

Back to top

2. List all named ranges and their cell references

This article shows you a way to display all named ranges you have in a workbook. This is a powerful feature in Excel if you have many named ranges and Excel defined tables and you want a better overview.

Instructions on how to build a list with all your named ranges:

  1. Go to "Formula" tab on the ribbon.
  2. Press with mouse on "Use in Formula" button on the ribbon.
  3. Press with mouse on "Paste Names...", a dialog box appears.
  4. Press with left mouse button on "Paste List" button on the dialog box.
  5. The list of named ranges is created.

The image below shows the named ranges in the "Name Manager" dialog box, they correspond to the list show in the image above.

Back to top