Excel 2007 Tables

I'd like to point out a wonderful Excel 2007 enhancement called Tables.  The secret to their power is that they automatically create dynamic named ranges. If that doesn't mean a lot to you, please take a moment and check out the full article for the details.

An Excel Table automatically creates a related named range. But, not just a normal named range ... a dynamic named range. That is, a new record entered directly under the Table causes both the Table and its related named range to automatically expand to include the new record. Therefore, any formulas, Pivot Tables, functions or charts that refer to the Table's name will automatically include the newly inserted data.

I can see you are not nearly excited enough about this feature, so here is a quick walk-through.

Let's say you have some data. In this simple example, you have some historical stock transactions for GE, like the screenshot below.

With the data, you'd like to be able to write some formulas on it, perhaps a lookup.  Or, maybe you want to do a Pivot Chart and analyze the data.  Or, perhaps you want to prepare trend lines in charts, or use Pivot Charts.  In essence though, you want to be able to use the data.  So, you proceed, and you create functions, formulas, Pivot Tables and Pivot Charts on the range of data, using row/column references for the range.  Specifically, you use the range reference of B12:H7579 in your functions, formulas, and Pivot Tables.  For example, you create a Vlookup() function as shown below, and you use the traditional range reference.

And life is good...until you add the next day's data.  When you add the next day's data under the table on the next available row, the formula doesn't automatically adjust to include the new data record.  Of course, if you had inserted a new Excel row somewhere within the original range the newly inserted row would be included in the formula.  But, you don't insert a new row, you simply type the new data in the next available row, for example, row 7580.  Since the formula doesn't automatically adjust its range reference to include the new data record, you have to manually update the formula.  This is bad for two reasons (1) it is inefficient (2) and it opens up potential for errors.

Now this issue, which is common, can be solved with Excel Tables.

The first step is to convert the data range into a "Table" by clicking any cell in your data range and then selecting the Insert > Table ribbon icon.  Your data range will be converted to a Table, as shown below.

This Table has a named range associated with it, which you can see or change by selecting Table Tools > Design as shown below.

From now on, instead of creating formulas, functions, Pivot Table, etc on a range reference (like B12:H7579) you would create them based on the Table's name, like Table1.  For example, in our Vlookup() function, we would use the Table's name reference, Table1, as shown below.

That way, as new records are added below the Table, Excel automatically extends the Table and its name (Table1) to include the new data.  This feature enables you to create bulletproof formulas, Pivot Table, charts and so on, which don't require manual maintenance as new data is added directly under your data range.  Booya!

Advanced
In addition to the named range concept discussed above, there is an enhanced syntax for referencing rows and columns inside of a Table which make it very hand for using Table data in formulas and functions.  For example, to refer to the "Open" column in a formula, simply use the syntax TABLENAME[COLUMNNAME], for example =Table1[Open].  To write a reference to the current Table row, use the syntax TABLENAME[#This Row], for example =Table1[#This Row].  To refer to the current row, but only a range of columns, use the syntax TABLENAME[[#This Row],[FIRSTCOL]:[LASTCOL]], for example =Table1[[#This Row],[Open]:[Close]]  Check the Excel help files for more on this.

Practice
If you want to play, I've included an Excel file called tables2007.xlsx which you can download using the link below.