Breaking News
Home / Tips and Tricks / Create a dynamically defined range in Excel

# Create a dynamically defined range in Excel  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 . ## 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.