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 create 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 cell K6, and then use that formula to create a named range in the next step. The OFFSET function takes five arguments: reference, rows, columns, height, and width. In this case the reference, or starting point, is cell B6, which we want to make absolute. When you’re creating dynamic named ranges, you must use absolute references. We don’t want any offset for rows and columns, so both of these arguments are “0”. However, we do need to provide a 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’s returning a range which, in this case, is an array of cells. However, if I evaluate the formula with F9, you can see that the array is there. Now let’s use this formula to make the dynamic named range. I do that by copying the formula and then creating a new name that uses this formula for the reference. When you’re working with dynamic ranges based on formulas, a good tip to remember is that you can get Excel to show you 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 that there’s something wrong with the formula. 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 the COUNTA function in place of the hard-coded value “10”. I’ll assume, in this case, that we won’t have any 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. Otherwise, you’ll get inconsistent results. 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 recalculate whenever changes are made to the worksheet. For a smaller table, this won’t cause any performance issues, but if you’re working with a lot of data, you may want to use INDEX or Excel’s built in Table feature to create the Dynamic range.
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.
