Use Excel’s OFFSET and COUNTA functions to create dynamic range names that automatically adjust as data is added or deleted.
Normally a named range refers to a fixed set of cell references, for example, $B$7:$B$12. However, if the OFFSET formula is used to calculate the cell reference, the range name will change as the data is added or deleted.
The syntax for an OFFSET formula is,
The COUNTA function is used to calculate the [height] or [width] of filled cells where the data will be stored. For example, in the figure below, typing a date in cell B13 expands the range name rngVDate from $B$7:$B$12 to $B$7:$B$13. Similarly, typing a number in cell C13 expands the range name rngVSales.
Click here to download the Excel tutorial example file. The example includes both horizontal and vertical data.
Click here to learn about the mistake lots of Excel users make with OFFSET. Do you make this mistake!
In the figure above, the OFFSET formula that calculates the cell reference of date information looks like,
The first term, wrksht!$B$7, is the top left cell for the calculated reference. It is “offset” by 0 rows and 0 columns.
The height of the calculated cell reference is COUNTA(wrksht!$B$7:$B$16). COUNTA examines all cells in the range $B$7:$B$16 on the worksheet named “wrksht” and counts how many are filled with text or numbers. (Yes, COUNT could have been used to count only numbers.) Finally, the [width] of the calculated range is 1.
New data added in cell B13 will expand the calculated Date reference.
Only add data contiguous to existing data.
If data is added outside the range examined by COUNTA, $B$7:$B$16, then it won’t be included in the range.
You can see and select the range of cells a dynamic range name refers to. But, it takes one of those simple and arcane tricks that put you above the average Excel users.
To see the dynamic range your OFFSET formula refers to press F5. The GoTo dialog box appears. However, the dynamic range name you created doesn’t show because it is a named formula, not a named cell reference. Type in one of the dynamic range names you created, e.g., rngVSales, and press Enter. The range of cells calculated by your OFFSET function should be selected.
If too many or not enough cells are selected, then your OFFSET formula is incorrectly calculating the range reference. Fix it by going to the Formula tab, in the Defined Names group open the Name Manager and edit the OFFSET formula for the range name you created.
Range names set are one of the most valuable things you can learn to create Excel worksheets that are easy to understand and change. They set a beginners work apart from professional developers. Here’s some tips on creating and using Excel range names.
Click here to learn quickly create range names.
Click here to learn how to create range names with a structured naming convention.
Click here to learn how to create ranges names you can double check.