Chapter 21: Managing Information in Tables

Overview

Microsoft Office Excel 2007 introduces a whole new set of features for managing information in tables. You'll find these features invaluable for almost any kind of tabular work-whether it be a simple list of names and phone numbers or something much more complex, such as a list of transactions that includes tax or discount calculations, subtotals, and totals. The new features make all the typical tabular manipulations-sorting, filtering, analyzing, formatting, and even generating charts based on tables-easier than they've ever been.

Here are just some of the advances that Office Excel 2007 has made in the area of table management:

• Autoexpansion If you add a row directly below the last row of a table or add a column directly to the right of a table, the table expands to incorporate the new row or column. All table styles, conditional formatting, calculations, and data validation rules extend to the new row or column. Charts based on data from the table also are similarly updated. Likewise, if you add a new column adjacent to the table, the new column is automatically incorporated into the table definition.

• Structured referencing Formulas that reference elements of a table can use column names and other tags in place of ordinary cell addresses. This kind of referencing, exemplified in Figure 21-1, makes table calculations self-documenting and enhances reliability.

• Sorting improvements Older versions of Excel let you sort data on as many as three criteria. In Excel 2007, you can sort on as many criteria as you please. You can also sort data based on the font or fill colors assigned to cells.

• Filtering improvements It's easier now to filter a table so you see only the rows in which you're currently interested. You can filter on multiple criteria or on icon sets applied via conditional formatting. You can also use filters based on dynamic date definitions, such as last week or the current quarter.

• Formula replication If you add a column to a table that performs calculations based on table data (a column such as the one that generates total scores in Figure 21-1), Excel automatically replicates the calculation formula throughout the column.

• Removal of duplicate data A simple command lets you highlight and (optionally) remove duplicate rows from a table.

• Table styles Excel 2007 comes with a large library of styles that you can use to apply gorgeous and consistent formatting to your tables. The styles are intelligent and dynamic. If you use a style that adds banding to a table (displaying alternate rows in contrasting colors), the banding adjusts correctly to changes in sorting, filtering, and table dimension. You can also create your own table styles or customize any of the existing styles.

Figure 21-1: Structured referencing, exemplified by the formula in E2, makes calculations easier to understand and less prone to error.

Note 

If you used older versions of Excel, you'll notice some terminology changes in Excel 2007. What was formerly called a list is now a table. (You can still press Ctrl+L to turn a range into a table, but Excel 2007 provides a new shortcut, Ctrl+T. The two shortcuts have the same function.) The term AutoFilter has been replaced by the simpler Filter. And the AutoFormat command of yore is gone, replaced by Table Styles.