O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #42. Create Ranges That Expand and Contract

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 Excels OFFSET function (if you haven't already). The 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 following:

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

Now click Add, then OK.

Tip

When defining the range for COUNTA, resist the temptation to include an entire column of data so that you do not force the 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required