Microsoft Excel 2007 - Excel Tables

Summarising And Emphasizing Related Data

Tables are special objects in Excel and you can use them to summarise and emphasize related data. Excel tables give you the ability to manage their contents independently of the rest of the worksheet. To create an Excel Table, select the range of cells you want to format and then click Home > Styles > Format as Table, and select a style.

The table columns are automatically given headings with auto-filters applied to them by default. You can rename the column headings by typing over them. Notice the Table Tools contextual tab that appears while we have the table selected. Although tables offer many features beyond formatting cells, many people use them simply to improve the appearance their information. An indispensible feature of some of the table styles available is automatic banding (see below). The banding of rows and columns can be toggled on and off using the corresponding check boxes in Table Tools > Design > Table Style Options.

Features Of Excel Tables

Excel tables have the following features that help you manage and analyse your data:

Sorting And Filtering

By default, dropdown lists are added to column headings to allow you to both filter and sort the contents of that column. You can sort on as many criteria as you please. Follow this link to find out more about sorting and filtering in Excel 2007.

Table Styles

When an excel table is selected, the Table Tools contextual tab appears and on it is the Table Styles group. You can use the large library found here to apply ready made styles to your Excel tables.

Autoexpansion

If you add a row beneath the last one in your table, or add a column to the right of the rightmost one, the table will automatically expand to incorporate the new addition. Any styles, data validation, calulations etc will be applie to the new row or column. Also, any charts that use the table as a source of data will be updated to reflect the changes. Alternatively, if you want to manually resize the table (larger or smaller) you can drag the handle at one of the corners of the table and drag in any direction.

Removal Of Duplicates

With the table selected, click Table Tools > Design > Tools > Remove Duplicates. A window like the one below will allow you to select which columns contain duplicates.

My Excel table was very simple, containing only a Product and Price column, but all columns present in your table will be displayed for you to select. On clicking OK, any duplicates in the table are removed.

Total Rows

Excel tables give you the ability to add a total row that calculates data totals in real time. Select any cell within the table and then click Table Tools > Design > Table Style Options, and check the Total Row box.

Creating Custom Table Formats

You can create your own table formats by clicking Home > Styles > Format as Table > New Table Style.

Give the table style a name and then select each item in the list. Click Format and then specify the font, borer and fill attributes you would like to see applied to that item. Once finished, your new table style will appear in the Format As Table palette in the Custom category.