Mastering Excel: Techniques for Building Adaptive Calculated Ranges Efficiently

Quick Links

• Create a Dynamic Defined Range in Excel
• Create a Two Way Dynamic Defined Range

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.

By using a dynamic defined range, you will not need to manually edit the ranges of your formulas, charts, and PivotTables when 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 it is a more efficient approach. OFFSET is a volatile function and can slow down large spreadsheets.

Create a Dynamic Defined Range in Excel

For our first example, we have the single-column list of data seen below.

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

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

Type “countries” 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 into a spreadsheet cell and then copying it into the New Name box is sometimes quicker and easier.

How Does This Work?

The first part of the formula specifies the start cell of the range (A2 in our case) and 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-blank cells in column A (six in our case).

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

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

The final result is $A$2:$A$6, and because of the COUNTA function, it is dynamic, as it will find the last row. You can now use this “countries” defined name inside a Data Validation rule, formula, chart, or wherever we need to reference the names of all the countries.

Create a Two Way Dynamic Defined Range

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

In this example, we will be using the data shown below.

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

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))

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

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

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

Also read: