Dynamic Named Ranges in Excel

When using named ranges for pivot tables (among other things), I find myself constantly having to update the area of the spreadsheet referred to by the named range. This renaming occurs as a result of my adding new raw data to the table, or editing the table's Column/Row layout. When researching possible solutions, I came across this article at Ozgrid. The gist of it is to utilize the "OFFSET" function to select the range's dynamic limits. By managing the Column and Row Count with the OFFSET function, each change to the data or the table is reflected in the range. Here is the formula to enter into the "Refers To" box of the named range setup:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

A few things to keep in mind when using this approach;

• Make sure your data is anchored at cell A1. Adjust the formula as needed if the anchoring (the upper left corner of your range) is located elsewhere.


• Make sure your data range is "clean" meaning that each cell in the Header ROW and the Header COLUMN (first column) has data in it. Blank cells will result in the "COUNTA" function used above to return an erroneous number.


• Delete any extraneous data below and to the left of the intended range.


• The Named Range will NOT appear in the Dropdown Box right above the A1 cell that gives us quick access to select named ranges; its primary use will simply be in formulas.