If you need to constantly update and add to your data, or if you work with charts and PivotTables, you'll want to create dynamic named ranges, which expand and contract relative to your data.
To understand how dynamic
named ranges function, first you should familiarize yourself with
OFFSET function (if you
OFFSET function is one of
Excel's Lookup and Reference functions.
We'll start off with the simplest of dynamic named ranges, one that will expand down a single column, but only as far as there are entries in that column. For example, if column A contains 10 continuous rows of data, your dynamic named range will incorporate the range A1:A10. Follow these steps to create a basic dynamic named range.
Select Insert → Name
→ Define, and in the Names in Workbook: box, type
MyRange. In the Refers To: box, type the
Now click Add, then OK.
When defining the range for
COUNTA, resist the
temptation to include an entire column of data so that you do not
COUNTA function to count potentially
thousands of unnecessary cells.
Now, provided that you have some data in column A, this named range will incorporate all the data in continuous rows, starting from cell A1. If you want to check a dynamic named range, you can do so in a few ways.
Unfortunately, dynamic named ranges are not available via the standard Name box, immediately to the left of the ...