Generally your Excel data changes frequently, so it is very useful to create a dynamic defined range that automatically expands and contracts to the size of your data range. Here’s how to create a dynamic defined range in Excel Sheet.
By using a dynamic defined range, you will not have to manually edit the ranges of your formulas, charts, and PivotTables when data changes. All of this will happen automatically.
There are two formulas used to create dynamic ranges: OFFSET and INDEX. This guide will focus on using the INDEX function as it is a more efficient approach. OFFSET is a volatile function and can also slow down large spreadsheets.
How to Create a Dynamic Defined Range in Excel
We have the single-column list of data seen below for our first example.
We need this to be dynamic in case more countries are added or removed then, the range automatically updates.
For this example, we have to avoid the header cell. Such as, we want the range $A$2:$A$6, but dynamic. Do this by tapping on Formulas > Define Name.
After that type “countries” in the “Name” box and then enter the formula below in the “Refers to” box.
By 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 mentions the start cell of the range (A2 in our case) and then the range operator (:) follows.
Using the range operator usually 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 which six in our case.
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 due to the COUNTA function, it is dynamic, as it will find the last row. Now you can 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 dynamic in height only. However, with a slight modification and another COUNTA function, you can also create a range that is dynamic by both height and width.
In this example, we will be using the data shown below.
Now we will create a dynamic defined range, which includes the headers. Tap on Formulas > Define Name.
After that Type ‘” sales” in the “Name” box and enter the formula below in the “Refers To” box.
This formula uses $A$1 as the start cell. Then the INDEX function 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 that make it dynamic in both directions. Although this formula started with A1, you could have specified any start cell.
Now you can use this defined name (sales) in a formula or as a chart data series to make them dynamic.