Excel 2007 updated a little used feature of Excel 2003 called Lists to create the functionality behind the Format As Table icon on the Home Ribbon tab.
The icon does so much more than just apply a format to a table. The table it creates is handled like a database by Excel and automatically expands as more data is added.
Excel creates table names that are very similar to range names and can be used in formula just like range names. They have one distinct advantage over range names. The table names are dynamic and automatically adjust as data is added to, or removed from, the table.
The formatted table’s range will expand if a new field is added to the column on the right of the table.
Another great feature is that any formulas entered, or formats applied, in a cell are automatically copied to all other cells in that field, no matter which cell is changed.
Other features include
- Filter icon is automatically added to the column headings
- Field names become column headings as you scroll down the page – they replace the column letters (cool!)
- A Pivot Table based on a formatted table automatically has its data extended as rows or columns are added. You still need to click Refresh to update.
- A new Ribbon tab is displayed when the table is selected. This allows you to name your table and to add totals and control some formatting options.
- Additional right click options when you right click in the table
- You can’t use the merged cell format in a table (I consider that an advantage)
Limitations of Formatted Tables
- You have to be careful copying/dragging formula containing table names. If you use the Fill Handle to drag a formula the table names act as if they are relative references and can change as you drag. In most cases this is not what you expect. Use copy and paste instead.
- The Subtotals option is not available in the Data Ribbon with a formatted table
- You can’t use Excel’s Share feature on a workbook containing a formatted table
- Multi-cell array formulas are not permitted in a formatted table
- Sheet protection can affect table functionality
- You can’t use Custom Views (View ribbon tab)
In Part 2 I will demonstrate some of the above features – it includes a video at the bottom of the post.