Sorting Tables and Other Ranges
MICROSOFT Excel 2010 offers an extensive 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.
If you are coming to Excel 2010 from a version prior to Excel 2007, here are some of the advances in table management that you will enjoy in the current version:
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 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 22-1, makes table calculations self-documenting and enhances reliability.
Sorting improvements Versions of Excel prior to 2003 let you sort data on as many as three criteria. Now you can sort on as many criteria as you please. You can also sort data on the basis of the font or fill colors assigned to cells.
Filtering improvements It’s easier now to filter a table so that 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 that performs calculations based on table data (a column such as the one that generates total scores in Figure 22-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 2010 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 dimensions. You can also create your own table styles or customize any of the existing styles.
Note
In versions of Excel prior to 2007, what now is called a table was called a list. You can still press Ctrl+L to turn a range into a table, but Excel 2010 provides an additional shortcut, Ctrl+T. The two shortcuts have the same function. The term AutoFilter, used in earlier versions, has been replaced by the simpler Filter. And the erstwhile AutoFormat command is gone, replaced by Table Styles.
In a sense, you can call anything you put in a contiguous block of spreadsheet cells a table, but in Excel the term has a more specific meaning. It refers to a block of data organized so that each row refers to an item (a person in an address list, a sale in a transaction log, a product in a product catalog, and so on) and each column contains one piece of information about that item (for example, the postal code of a contact, the date of a sale, or the catalog number of a product). In addition, for a block of data to become a table, you have to designate it as such. (See Creating a Table, next.)
Typically, the worksheet range defined as a table should have the following characteristics:
The top row should consist of labels, with each label describing the contents of the column beneath it. Each label should be unique. (The labels row is not mandatory, but if you omit it, Excel generates one for you by using default column names.)
Each column should contain the same kind of information.
Each category of information you want to be able to sort by, search on, or otherwise manipulate individually should occupy a separate column.