How to Use Dynamic Named Ranges in Excel

Dynamic named ranges in Excel are flexible ranges that automatically change in size as you add or remove data. They’re especially useful when you want to work with data sets that grow over time, like sales reports, inventory lists, or survey responses. For example, you might use a dynamic named range to automatically update a chart as new monthly revenue figures are entered to ensure your outputs always capture the latest data. 

You can use dynamic ranges in formulas, charts, dashboards, and more to provide accurate results as your data updates in real time. Explore this exciting feature and learn when to use dynamic ranges, how to build your range using different functions, and follow step-by-step instructions to start using this feature.

Syntax breakdown

Learning each Excel function that contributes to creating dynamic ranges can help you build a deeper understanding of the underlying processes and how to progress through each step. Each of the following functions helps Excel locate, size, and adapt your range as your data set changes in size.

COUNTA

The COUNTA function counts how many non-empty cells you have in a given range. You can use this in a dynamic range formula to determine how many rows or columns should be included in your range. The original syntax is:

COUNTA(value1, [value2], …)

Value1 is required, while value2 is optional and represents additional values to count. You can continue to add up 255 arguments in your function. In this most basic sense, you can use this function to count the number of nonblank cells in a set range. For example, to count how many nonblank cells are in cells A1 to A5, you would type:

COUNTA(A1:A5)

For dynamic ranges, the cells you want to work with may change, but you can still use COUNTA to identify empty cells. If Column A in your data set had a growing list of entries, you could use COUNTA to return the number of populated rows, which you could then use to set your range values. For this example, your syntax would be:

COUNTA(Sheet1!$A:$A)

It’s worth noting that COUNTA is specific to text values, whereas COUNT is used for a column of numbers.

INDEX

The INDEX function returns a cell reference within a specific range based on the row and column numbers. In the formula, you have the array (or group of cells), the row number, and the column number. The plain syntax looks like this:

INDEX(array, row_num, [column_num])

For example, you could set your “array size” to be from rows 1 to 3 and columns A and B. You could then select the cell that was in the second row and second column of your array, which would be B2. Your syntax would look like:

INDEX(A1:B3, 2, 2)

When used with COUNTA, the INDEX function can help define the end points of a dynamic range. For example, if you had:

=Sheet1$A$1: INDEX(Sheet1$A:$A, COUNTA(Sheet1$A:$A))

This would be telling your function to start at cell A1 on Sheet1 (Sheet1!$A$1) and create a range starting at A1 and ending at the last non-empty cell in column A (Sheet1!$A$1: INDEX(...)). Your function does this by counting the number of non-empty cells in column A on Sheet1 (COUNTA(Sheet1$A:$A)), and then using the INDEX function to reference a specific cell in column A based on the row number provided. So if your COUNTA function returns 15, the function would become INDEX(Sheet1!$A:$A, 15), which returns A15. When putting the entire function together, you would return a range of A1:A15, if you had 15 filled rows in column A.

OFFSET

Another relevant function for dynamic ranges is the OFFSET function. The OFFSET function returns a reference to a range that is a defined number of rows and columns from the starting cell. You can define the height and width of this range, allowing your range to change as your data set expands or shrinks. The plain syntax would be:

OFFSET(reference, rows, cols, [height], [width])

If you wanted to specify that the range starts in cell A1 and then grows depending on how many rows are in column A, you would define the function as:

OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

This is telling your function to start in cell A1 on Sheet 1, stay in the original column without moving horizontally or vertically, set the height of the range to the number of non-empty cells, and set the width to one column.

What is static vs. dynamic range?

A static range refers to a group of fixed cells, such as A1:A15. This range will not change unless you manually update it. On the other hand, a dynamic range automatically adjusts as you add or remove data. This allows you to continually update your data and corresponding outputs without making manual adjustments each time.

How to use dynamic named ranges in Excel: Step by step

Creating a dynamic range allows your formulas and charts to adjust to your data automatically. For most versions of Excel (i.e., Microsoft Office Excel 2007 through more recent versions), you can create one using the Name Manager, a built-in tool in Excel, with functions like OFFSET, INDEX, and COUNTA. To create a dynamic range, follow these steps. 

1. Enter your data.

In your Excel worksheet, enter your data in a tabular format, using rows and columns.

2. Open the name manager.

Go to the Formulas tab, then click Name Manager. Once you’re here, click New to create your range. So, you go to Formulas > Name Manager > New.

3. Enter a descriptive name for your range.

To help you keep track of what your range represents, enter a clear name in the Name Manager. For example, you might choose “CustomerPurchases” or “SalesMade.”

4. Create your dynamic formula. 

In the “Refers to” box, create your formula to define your range. 

This is when you might type:

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

Or:

=Sheet1$A$1: INDEX(Sheet1$A:$A, COUNTA(Sheet1$A:$A))

Once you’ve created your formula, click Close.

5. Apply your dynamic range.

Once you have named and defined your range, you can use it within other Excel operations. For example, you might sum all of the values in your range by typing: =SUM(CustomerPurchases) in your Excel sheet.

Dynamic named range example

As a sales professional, you might have a column of sales prices in Column A and sales percentages in Column B. You might have dynamic ranges for these columns named “ProductPrice” and “SalesPercentage.” You could then have a column of the updated prices defined as “ProductPrice*SalesPercentage” that would continually update prices as you alter the price or sales percentage for each product.

When to use OFFSET vs INDEX for your dynamic range

You can choose to use both OFFSET and INDEX functions to create dynamic named ranges in Excel, each of which works differently. Determining the right one for you depends on the size of your workbook, your priorities, and how you anticipate expanding your range. 

When to use OFFSET

The OFFSET function allows you to specify the number of rows and columns to include, making it easier to expand your range both horizontally and vertically. This function also performs more effectively with small data sets, as it recalculates each time you make a change (i.e., it is a volatile function), which can slow your performance for large data sets. If you have a smaller workbook and want to expand in both directions, you might opt for the OFFSET function. 

When to use INDEX

The INDEX function is non-volatile, which makes it more effective for larger workbooks. However, the INDEX typically expands in only one direction, and the formula becomes more complex if you want your range to expand both horizontally and vertically. If you have a large workbook or only want to expand in one direction, you might opt for INDEX.

How to use dynamic array functions

In more recent Excel versions (Excel 365 and Excel 2021+), you can use dynamic array formulas, which allow values to “spill” into adjacent cells. This type of function is similar to dynamic ranges but automatically expands rather than relying on OFFSET or INDEX functions. 

This means that if your function returns more than one result, such as a list, Excel will populate the results into adjacent cells. Some examples include:

• Sequence(3): Excel will automatically fill three cells with your results, rather than one cell.

• Unique(A1:A5): Excel returns a list of unique values found in cells A1 through A5.

• Sort(A1:A10): Excel sorts your list and returns A1 to A10 in order.

If you want to refer to a spill range in your sheet, you can cite the first cell in the spill range followed by a number sign. For example, you could use =A1# to reference the entire spill range starting in A1.

New to Excel 365: TRIMRANGE

A new addition to Excel 365 is the TRIMRANGE function, which makes it even easier to manage your dynamic ranges without complex formulas. TRIMRANGE automatically removes empty rows and columns from your selected range, trimming your data set to a range that only includes populated data. As your data set changes in size, TRIMRANGE updates your range dynamically. 

For example, if you had a range of cells and wanted to remove empty rows and columns outside of your block of data. You could say:

=TRIMRANGE(A:F)

In this case, your TRIMRANGE function would automatically create a cleaner range by removing blank rows or columns outside of your data block between columns A and F. You can then use this in conjunction with other functions to create a dynamic range that only includes filled data. For example, you could have:

=LEN(TRIMRANGE(A:F))

With this, Excel would first trim your range down to only the populated cells and then output the length of characters in each cell in the trimmed range.

Using dynamic named ranges in Excel

To create a dynamic named range in Excel, follow these simple steps:

1. Enter your data.

2. Open the Name Manager and click New.

3. Input your formula into the Refers to box.

4. Press Close and apply the range as needed within your worksheet.

Learn how to use Excel on Coursera

Creating dynamic ranges in Excel allows you to reference your data and update your function results automatically as your data set changes. While this is one exciting feature of Excel, you can explore many other functions and uses with courses and Guided Projects on Coursera. For intermediate learners, consider taking the Introduction to Data Analysis using Microsoft Excel Guided Project on Coursera. If you prefer more didactic learning, you can take the Excel Basics for Data Analysis by IBM to explore how to perform basic spreadsheet tasks, including navigation, data entry, and using formulas.