Your Excel data changes frequently, so it’s helpful to create a defined dynamic range that automatically expands and contracts to the size of your data range. Let’s see how.
Using a defined dynamic range, you do not need to manually edit the ranges of formulas, charts, and pivot tables when the data changes. This will happen automatically.
Two formulas are used to create dynamic intervals: OFFSET and INDEX. This article will focus on using the INDEX function, as it is a more effective approach. OFFSET is a volatile function and can slow down large spreadsheets.
Create a dynamic range defined in Excel
For our first example, we have the single column data list seen below.
We need it 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. 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: INDICE ($ A: $ A, COUNTA ($ A: $ A))
Typing this equation into a cell on the spreadsheet and then copying it to the New Name box is sometimes quicker and easier.
How does this work?
The first part of the formula specifies the range start cell (A2 in our case) and then follows the range operator (:).
= $ A $ 2:
Using the interval operator forces the INDEX function to return an interval instead of the value of a cell. The INDEX function is then used with the COUNT function. COUNTA counts the number of nonblank cells in column A (six in our case).
ÍNDICE ($ A: $ A, COUNTA ($ A: $ A))
This formula asks the INDEX function to return the interval of the last blank cell in column A ($ A $ 6).
The end result is $ A $ 2: $ A $ 6 and, thanks to the COUNT function, it is dynamic because it will find the last row. You can now use this name defined by «countries» in a data validation rule, formula, chart, or anywhere we need to refer to the names of all countries.
Create a defined two-way dynamic range
The first example was only dynamic in height. However, with a slight change and another COUNT function, you can create a range that is dynamic in both height and width.
In this example, we will use the data presented below.
This time, we will create a defined dynamic range, which includes the headers. Click Formulas> Define Name.
Type «sales» in the «Name» box and enter the following formula in the «Refers to» box.
= $ A $ 1: ÍNDICE ($ 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 search 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 at A1, it could have specified any starting cell.
You can now use this defined name (sales) in a formula or as a series of graphical data to make it dynamic.