Posted by: Craig Hatmaker
database, development, dynamic ranges, excel, ListObjects, Microsoft Excel, ms query, odbc, sql, Tables, tutorial, vba
Dynamic named ranges have been a subject of interest on Excel boards for sometime. Lots of people want to know what’s the best way to create a named range that expands (or shrinks) according to the data entered, and can be used in formulas like VLOOKUP without change. For some background on this subject see: Dynamic Ranges – Overcoming Shortcomings. In that post I promised to talk about what I believe is truly, the one best way to handle this kind of thing, Excel’s Tables.
In 2003, Excel introduced something called a “ListObject” also known as a ”Table”. There are several features packed into Tables, but the main feature of importance in this discussion is their ability to grow or shrink dynamically. Out of the box, a table is a dynamic named range. So why agonize over what formula best creates one?
Creating tables is easy, simply select any cell within the desired range of data and press Ctrl-L (in Excel 2007 or 2010 you can also use Ctrl-T). Excel will probably guess correctly what range you meant but gives you the opportunity to specify exactly what you want. Whenever your selected cell is in a table Excel adds the Table Tools tab to its ribbon. There you can change the table’s name using the convenient “Table Name:” box in the upper left corner.
Unlike formula approaches, tables don’t mind other tables on a worksheet. And Tables come with right click menu functions for adding/inserting/deleting rows/columns. As you add rows or columns, the table automatically encompasses the new data, extends formats and formulas, and when rows are inserted, moves tables below to make room.
Tables are superior to named ranges in every way except one: they can’t be used as datasources. That’s easily overcome. Simply select the range you want to use as a datasource (that can be the entire table, just the data, a column…) and give it a name using the name box in the upper left corner of each worksheet, or using the name manager. The new name is every bit as dynamic as the best dynamic range formula – without the formula.
It’s Time to Move Ahead
At the time of this blog post, I still meet Excel fans enthusiastic over dynamic ranges. When asked “why not tables?” I usually get a blank stare. Some point out tables don’t work with all versions of Excel. True enough. But it’s been almost a decade now since XL 2003 came out. I’ve long since upgraded every user in the company I work for to Office 2003 or higher. I’ll bet your company has too. So put your favorite dynamic named range formula in a frame and hang it on the wall with other momentous from your past. It’s time to move ahead with tables.