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