# How to create a defined dynamic range in Excel

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. ## 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.

How to convert a Word document to an Excel spreadsheet