One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. Dynamic ranges are also known as expanding ranges - they automatically expand and contract to accommodate new or deleted data.
Note: OFFSET is a volatile function, which means it recalculates with every change to a worksheet. With a modern machine and smaller data set, this should't cause a problem but you may see slower performance on large data sets. In that case, consider building a dynamic named range with the INDEX function instead.
In the example shown, the formula used for the dynamic range is:
Note that height is being adjusted with -2 to take into account header and title values in cells B4 and B2. The advantage to 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:
This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. In the example shown, the named range "data...
The Excel OFFSET function returns a reference to a range constructed in parts: a starting point, a row and column offset, and a final height and width in rows and columns. OFFSET is handy in formulas that dynamically average or sum "last n values...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.