How to use Excel 2007 tables - Part 1

In this two part series, Simon Hurst looks in more detail at lists and Excel 2007 tables and how they might be beneficial in practice.

As discussed in my previous article on the subject, “tables” are new to Excel 2007, but hark back to a similar feature in Excel 2003 known as Lists. An Excel 2003 list could be created by right-clicking on a cell within the block of cells and choosing the Create List option. Like an Excel 2007 table, an Excel 2003 list has a totals row that can be toggled on or off, and automatically expands to incorporate data added immediately beneath or to the right of the list. A chart linked to the list will automatically reflect additional rows added to the list.

Excel 2007 tables build on these list features.

Creating a table

First of all you need to ensure that the data you want to turn into a table is in a suitable format. Generally each column will contain a different field of data and each row a separate record, very much like a database table. You can click on any cell in the block of data and choose the Table option from the Tables section of the Insert ribbon tab, or the Format as Table option from the Styles section of the Home ribbon tab.

You will be asked to confirm your data area and whether it has headers. The Insert, Table option will turn the area into a table and apply a default format; the Format as Table option will allow you to choose from a gallery of table formats.

Table features

Headings - If a table is longer than can be displayed in a single window, when you scroll down through the rows so the row that contains the headings is no longer visible, the headings will appear in the column letter area:

Automatic table expansion - A key feature of both tables and lists is their ability to “autoexpand” to accommodate additional data. For added data rows, when no totals row is present, you can just add details immediately beneath the existing data and it should be incorporated into the table or list. Data can be added either by typing or by pasting.

Things are a little more complicated when the totals row is toggled on. In an Excel 2003 list a 'new' row is included between the last data row and the totals row and new items can be entered or pasted into that row. In Excel 2007 no 'new' row is displayed but pasting over the totals row should insert the new data within the table rather than overwriting the totals row. It took me a while to work out the easiest way to manually enter another row in a 2007 table that has a totals row, but the answer actually comes from Word, where pressing the tab key on the last entry of a table generates a new row.

For columns, adding data to the immediate right of the table or list will cause the table or list to AutoExpand to include the additional column. A smart tag will also appear letting you choose whether to cancel this AutoExpansion or to stop Automatically Expanding Tables in the future:

Sorting and filtering - One area where 2007 is a significant improvement over 2003 is the additional sorting and filtering options. When you turn a block of data into a table or list, the header row will include drop downs for each column for sorting and filtering the table by the contents of that column. Excel 2007 adds the ability to sort by cell colour, font colour or cell icon as well as increasing the number of sorting levels available. In addition, there is a wide range of pre-set filters, relevant to the contents of the column. For example, here is the list of date filters:

Totals row - Adding a totals row to a table or list adds a row (no surprises there, then) that includes a total for the rightmost column of the table. Each cell in the totals row has a drop down that lets you choose the summary function you want to use: for example, the obvious sum or count but also average, min, max and many others. Note that the formulae created do not use the standard SUM(), COUNT() etc. functions but instead use the SUBTOTAL() function.

Your table should remember the various total settings in these cells if you toggle the totals row off and then back on again.

Selecting in a table - In both Excel 2003 and 2007 positioning the cursor at the top edge of a table or list column will change the cursor to a downward pointing selection arrow. Clicking this will select that column within the table. In 2007 the first click will select only the data and exclude the headings and totals. Clicking a second time goes on to select the whole column. 2007 also includes a Select option on the right-click menu within a table that gives the choices of selecting:
•    The table column data
•    The entire table column
•    The table row

Coming soon

In the second part of this article, we will look at how Excel 2007 uses structured formulae when working with data in tables and how this and the other table features can be useful in a range of practical situations.

Further reading

Focus on Excel 2007
Excel 2007 Tables - Not just a pretty format
ExcelZone Compendium: Sorting and filtering tips (Excel 2007 update)