How to Create a Dynamic Defined Range in Excel

Excel logo

Your Excel data changes frequently, so it’s useful to create a dynamic defined range that automatically expands and contracts to the size of your data range. Let’s see how.

Using a dynamic defined range, you will not need to manually change the ranges of your formulas, charts, and PivotTables when the data changes. This will happen automatically.

Two formulas are used to create dynamic ranges: OFFSET and INDEX. This article will focus on using the INDEX function as this is a more efficient approach. OFFSET is a volatile function and can slow down large spreadsheets.

Create dynamic defined range in Excel

For our first example, we have the list of data in a single column below.

Data range to energize

We need this to be dynamic so that if more countries are added or removed, the range is automatically updated.

For this example, we want to avoid the header cell. As such, we want the range $ A $ 2: $ A $ 6, but dynamic. To do this, click Formulas> Define Name.

Create a name defined in Excel

Type “country” in the “Name” box and then enter the formula below in the “Refers to” box.

= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))

Typing this equation in a spreadsheet cell and then copying it to the New Name box is sometimes faster and easier.

Using a formula in a defined name

How does this work?

The first part of the formula specifies the range start cell (A2 in our case), then the range operator (:) follows.

= $ A $ 2:

Using the range operator forces the INDEX function to return a range instead of the value of a cell. The INDEX function is then used with the COUNTA function. COUNTA counts the number of non-empty cells in column A (six in our case).

INDEX ($ A: $ A, COUNTA ($ A: $ A))

This formula requests the INDEX function to return the range of the last non-empty cell in column A ($ A $ 6).

The end result is $ A $ 2: $ A $ 6, and because of the COUNTA function, it is dynamic, because it will find the last line. You can now use this defined “country” name in a rule, formula, data validation chart, or wherever we need to reference the names of all countries.

Create a bidirectional dynamic defined range

The first example was only dynamic in height. However, with a slight modification and another COUNTA function, you can create a dynamic range both in height and in width.

In this example, we will use the data below.

Data for bidirectional dynamic range

This time, we will create a dynamic defined range, which includes the headers. Click Formulas> Define Name.

Create a name defined in Excel

Type “sales” in the “Name” box and enter the formula below in the “Refers to” box.

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

Bidirectional dynamic defined range formula

This formula uses $ A $ 1 as the starting cell. The INDEX function then uses a range from the entire worksheet ($ 1: $ 1048576) to find and return.

One of the COUNTA functions is used to count non-empty rows, and another is used for non-empty columns, which makes it dynamic in both directions. Although this formula started from A1, you could have specified any starting cell.

You can now use this defined name (sales) in a formula or as a series of chart data to make them dynamic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.