Summary

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. In the example shown, the formula below is used to create a dynamic named to encompass the data in B5:G10:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Note: this formula is meant to define a named range that can be used in other formulas. This range will be dynamic and expand and shrink with the data. An alternative to a formula-based dynamic named range is to use an Excel Table.

Note: OFFSET is a volatile function that can cause performance problems when used on large datasets or in more complicated workbooks. If you have trouble, consider building a dynamic named range with the INDEX function instead.

Generic formula

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Explanation 

Dynamic ranges are also known as expanding ranges because they automatically expand and contract to accommodate new or deleted data. You can see a video demo of this approach here. This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

The first argument in OFFSET represents the first cell in the data (the origin), which in this case is cell B5. The next two arguments are offsets for rows and columns and are supplied as zero.

The last two arguments represent height and width. Height and width are generated on the fly by using COUNTA, which makes the resulting reference dynamic.

For height, we use the COUNTA function to count non-empty values in the range B5:B100. This assumes no blank values in the data, and no values beyond B100. COUNTA returns 6.

For width, we use the COUNTA function to count non-empty values in the range B5:Z5. This assumes no header cells, and no headers beyond Z5. COUNTA returns 6.

At this point, the formula looks like this:

=OFFSET(B5,0,0,6,6)

With this information, OFFSET returns a reference to B5:G10, which corresponds to a range 6 rows height by 6 columns across.

Note: The ranges used for height and width should be adjusted to match the worksheet layout.

Variation with full column/row references

You can also use full column and row references for height and width like so:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Note that height is being adjusted with -2 to take into account header and title values in cells B4 and B2. The advantage of this approach is the simplicity of the ranges inside COUNTA. The disadvantage comes from the huge size full columns and rows — care must be taken to prevent errant values outside the range, as they can easily throw off the count.

Determining the last row

There are several ways to determine the last row (last relative position) in a set of data, depending on the structure and content of the data in the worksheet:

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.