قالب وردپرس درنا توس
Home / Tips and Tricks / Create a dynamically defined range in Excel

Create a dynamically defined range in Excel



  Excel logo

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.

 Making data range dynamic

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.

 Create a defined name in Excel

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 .

 Use a formula in a defined name

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.

 Data for a bidirectional dynamic range

This time we create a dynamically defined range, including the headers. Click Formulas> Define Name.

 Create a defined name in Excel

Type & # 39; & # 39; sales & # 39; in the & # 39; Name & # 39; and enter the formula below in the & # 39; Refers To & # 39; in. [19659012] = $ A $ 1: INDEX ($ 1: $ 1048576, COUNTA ($ A: $ A), COUNTA ($ 1: $ 1))

 Two-way dynamically defined range formula [19659003] 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.




Source link