Your Excel data changes regularly, so it is useful to create a dynamically defined range that automatically increases and decreases to your data range. Let's see how.
By using a dynamically defined range, you do not have to manually edit the ranges of your formulas, charts and pivot tables when data changes. This is done automatically.
Two formulas are used to create dynamic ranges: OFFSET and INDEX. This article will focus on the use of the INDEX function because this is a more efficient approach. OFFSET is a volatile function and can delay large spreadsheets.
Create a dynamically defined range in Excel
For our first example we have the list with some columns of data below.
We must have this 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 $ A $ 2: $ A $ 6 range, but dynamic. Do this by choosing 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 to the New Name box is sometimes faster and easier .
How does this work? The first part of the formula indicates the range starting cell (in this case A2) and then the range operator (:) follows.
= $ A $ 2:
The operator 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 prompts 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 finds the last row. You can now use this defined name for "countries" in a data validation rule, formula, chart or wherever we need to refer to the names of all countries.
Creating a two-way dynamically defined range
The first example was only dynamic in height. However, with a small change and another COUNTA function, you can create a range that is dynamic in both height and width.
In this example we use the information below.
This time we create a dynamically defined range, including the headers. Click Formulas> Define Name.
Type & # 39; & # 39; sales & # 39; in the & # 39; Name & # 39; and enter the formula below in the & # 39; Refers To & # 39; in.  = $ 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 view and return from.
One of the COUNTA functions is used to count the non-empty rows and another is used for the non-empty rows of columns, making it dynamic in both directions. Although this formula is started from A1, you can specify any starting cell.
You can now use this defined name (sales) in a formula or as a chart data series to make them dynamic.