Your Excel data changes frequently, so it makes sense to create a defined dynamic range that will automatically expand and contract to the size of your data range. Let’s see how.
By using a defined dynamic range, you don’t have to manually change the ranges of your formulas, charts, and pivot tables as the data changes. This happens automatically.
Two formulas are used to create dynamic ranges: OFFSET and INDEX. This article focuses on using the INDEX function, which is a more efficient approach. The OFFSET function is volatile and can slow down large spreadsheets.
Create a defined dynamic range in Excel
For our first example, we have the single-column list of data below.
We need a dynamic system so that the range is automatically updated as more countries are added or removed.
For this example we want to avoid the header cell. So we want the range $A$2:$A$6, but dynamically. To do this, click Formulas > Set Name.
Enter “country” in the “Name” field and then enter the following formula in the “References” field.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Sometimes it’s quicker and easier to type this equation in a table cell and then copy it into the New Name field.
The first part of the formula specifies the starting cell of the range (A2 in our case) and then the range operator ( 🙂 follows.
=$A$2:
Using the range operator causes the INDEX function to return a range instead of a cell value. 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 tells the INDEX function to return the range of the last nonblank cell in column A ($A$6).
The end result is $A$2:$A$6, and thanks to the COUNTA function, it’s dynamic because it finds the last row. You can now use this defined “country name” in a data validation rule, formula, table or anywhere we need to reference the names of all countries.
Create a defined two-way dynamic range
The first example was just altitude dynamics. However, with a slight modification and another COUNTA function, you can create an area that is dynamic in both height and width.
In this example we will use the data shown below.
This time we’ll create a defined dynamic range that includes the headers. Click Formulas > Set Name.
Type “Sales” in the Name field and enter the following formula in the References field.
=$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 of the entire worksheet (1:1048576) to search up and back.
One of the COUNTA functions is used to count non-blank rows and another is used for non-blank columns, making it dynamic in both directions. Although this formula starts at A1, you 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.