Quick, clean, and to the point

How to create a dynamic named range with OFFSET

In this video, we'll look at how to create a dynamic named range using the OFFSET function.

Here is the same data we looked at earlier, which contains information for 10  properties.

To create a dynamic named range that refers to this data using the OFFSET function, first identify the first cell of the data in the upper left.

In this case, that's cell B6.

To created a named range, we're going to use the name manager. However, it will be easier to enter the formula using the formula bar, so I'll start by entering the OFFSET function in K6, and then use that formula to create a named range in the next step.

The OFFSET function takes 5 arguments: reference, rows, columns, height, and width.

The reference or starting point is B6, which we want to make absolute. When you're created dynamic names ranges, you must use absolute referencing.

We don't want any offset for rows and columns, so both of these arguments are zero.

However, we do need to provide height and width. To reference the full set of data, we need to provide a height of 10 rows and a width of 8 columns.

If I enter the OFFSET formula as-is, we'll get a VALUE error, since it is returning a range which in this case is an array of cells. However, if I evaluate the formula with F9, you can see array is there.

Now let's use this formula to make a dynamic named range. I do that by copying the formula and then creating a new name that uses the formula for the reference.

When you're working with dynamic ranges based on formulas, a good tip is that you can get Excel to show you the the range on the worksheet, by clicking out of the reference area, and back again. If the moving dashed lines are not in the right place, you know there's something wrong with the formulas.

So, we now have a named range based on offset, but it's not dynamic yet. If I add properties to the list, they aren't included.

To make the range dynamic, we need to make the height dynamic. We can do that by using the COUNTA function in place of the hard-coded value 10.

I'll assume that we won't have more than 20 properties in the list, so I'll use the range B6:B25 inside COUNTA.

COUNTA counts non-empty cells, so it's important to use it on a column that always contains values.

Now if I paste new data below, the range expands to include it.

If you want to make the width of this range dynamic as well, you can follow the same approach that we used for height, counting non-empty cells in a range that includes the table header.

Dynamic ranges that use OFFSET work well, but remember that OFFSET is a volatile formula that will recalculated whenever changes are made on in the workbook. For a smaller table, this want cause any performance issues, but if you are working with larger tables you may want to use INDEX or Excel's built in Table to create the Dynamic range.

Dave Bruns