Dynamic range names

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Dynamic range names
Creating named ranges in Excel can make your formulas much easier to create and read, and we encourage people to use them often. Range names do have one big limitation, however: they don't automatically expand if you add new data to the worksheet. This blog shows you a couple of ways to create dynamic range names to get around this problem.

Posted by Andrew Gould on 24 April 2025

In this blog

Formulas with range names
The limitation of range names
Assigning a formula to a named range
Using the OFFSET and COUNTA functions
Using the TRIMRANGE function
The result

Formulas with range names

Using range names is a great way to make your Excel formulas easy to write and read (see this blog or this video if you'd like to learn how to create named ranges).

Using named ranges in a formula in Microsoft Excel

This formula uses two range names to calculate the Profit for a list of films.

Entering the formula shown above creates a spilled array, calculating an answer for each row of the input ranges.

A spilled array formula in Microsoft Excel using named ranges

The array of answers spills into the neighbouring cells.

The limitation of range names

Unfortunately, a range name doesn't automatically expand to include new data added next to it.

A formula using range names in Microsoft Excel

Simply adding new data to the end of each range doesn't work.

You could force a range name to expand by inserting a new row between the top and bottom of the range.

We could make the range names reference all the cells we may potentially add data to in the future, but this causes its own problem:

Excel calculates a result even if the range names refer to empty cells.

Assigning a formula to a named range

We'd like to make each range name dynamically change its size when data is added to the worksheet. To do this, we need to create a formula to return the range of populated cells and assign it to the range name. Start by choosing Formulas | Name Manager from the Excel ribbon.

The Name Manager dialog box in Microsoft Excel

Select a range name from the list then edit its formula in the Refers to: box.

There are two main ways to write the formula to create a dynamic range name, as detailed below:

Using the OFFSET and COUNTA functions

If you're working with an older version of Excel, you can use a combination of the OFFSET and COUNTA functions.

The COUNTA function counts the non-blank cells in a range. In the example below, we're counting the non-blank cells in column B (excluding cell B1):

Using the COUNTA function in Microsoft Excel to count the non-blank cells in a range.

There are 3 non-blank cells below B1 in this worksheet.

The OFFSET function allows you to point to a starting cell, move a number of rows and columns away from that cell, and return a range of cells with a specified height and width. Here's the syntax of the function:

=OFFSET(Starting cell, Rows to move down, Columns to move right, Number of rows to include, Number of columns to include)

To create a dynamic range which includes all the Gross numbers, we want to:

Start in cell B2.
Move 0 rows down.
Move 0 columns to the right.
Include a number of rows equal to the count of non-blank cells in the column (excluding B1).
Include 1 column.

Here's a formula to return the range of Gross numbers:

=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B$2:$B$1048576), 1)

We can assign this formula to the Gross range name using the Name Manager:

Assigning a formula to a range name in Excel to create a dynamic range name

Enter the formula in the Refers to: box and click the tick to commit it.

We can do the same thing for the Budget range name using the following formula:

=OFFSET(Sheet1!$C$2, 0, 0, COUNTA(Sheet1!$C$2:$C$1048576), 1)

Using the TRIMRANGE function

If you're using an up-to-date version of Excel, you can use the TRIMRANGE function to make the calculation simpler. The TRIMRANGE function allows you to reference a large range of cells and ignore the blanks (you can read more about the function in this blog).

The formula for the Gross range name is:

=TRIMRANGE(Sheet1!$B$2:$B$1048576)

The formula for the Budget range name is:

=TRIMRANGE(Sheet1!$C$2:$C$1048576)

You can make the formula even shorter by using a Trim Ref instead of the TRIMRANGE function. To do this, place a full stop on each side of the colon in the cell reference. Here's the formula for the Gross range name:

=Sheet1!$B$2.:.$B$1048576

The result

Whichever style of formula you use, you should find that the range names will automatically grow and shrink as you add or remove data.

A formula using dynamic range names in Microsoft Excel

The formula calculates a value for each populated cell in each range.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS