How to create a dynamic named range with OFFSET

In this case, that’s cell B6.

To create anamed range, we’re going to use the Name Manager.

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

When you’re creating dynamic named ranges, you must useabsolute references.

However, we do need to provide a height and width.

Now let’s use this formula to make the dynamic named range.

So, we now have a named range based on OFFSET, but it’s not dynamic yet.

If I add properties to the bottom of the list, they aren’t included.

To make the range dynamic, we need to make the height dynamic.

We can do that by using theCOUNTA functionin place of the hard-coded value “10”.

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

Otherwise, you’ll get inconsistent results.

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