One of the most significant new features in Excel 2007 is tables. A table is a rectangular range of data that usually has a row of text headings to describe the contents of each column. Excel, of course, has always supported tables. But the new implementation makes common tasks much easier—and a lot better looking. More importantly, the new table features may help eliminate some common errors.
This chapter is a basic introduction to the new table features. As always, I urge you to just dig in and experiment with the various table-related commands. You may be surprised at what you can accomplish with just a few mouse clicks.
A table is simply a rectangular range of structured data. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a bank transaction, an employee, a product, and so on. Each column contains a specific piece of information. For example, if each row contains information about an employee, the columns can contain data such as name, employee number, hire date, salary, department, and so on. Tables typically have a header row at the top that describes the information contained in each column.
So far, I’ve said nothing new. Every previous version of Excel is able to work with this type of table. The magic happens when you tell Excel to convert a range of data into an “official” table. You do this by selecting any cell within the range and then choosing Insert 
Tables Table.
When you explicitly identify a range as a table, Excel can respond more intelligently to the actions you perform with that range. For example, if you create a chart from a table, the chart will expand automatically as you add new rows to the table.
Note
Excel 2003 introduced a rudimentary version of the table feature. In that version, tables were known as lists. The implementation in Excel 2007 is much better.
Figure 6.1 shows a range of data that has not yet been converted to a table. Notice that this range corresponds to the description I provide earlier: It’s a range of structured data with column headers. In this example, each row contains information about a single real estate listing. The range has 10 columns and 125 rows of data (plus a descriptive header row).
Figure 6.2 shows the range after I converted it to a table by choosing Insert Tables Table.
On the CD-ROM
If you’d like to practice working with tables, the workbook shown here is available on the CD-ROM. The file is named real estate table.xlsx.
What’s the difference between a standard range and table?
Activating any cell in the table gives you access to a new Table Tools context tab on the Ribbon (see Figure 6.3).
The cells contain background color and text color formatting. This formatting is optional.
Each column header contains a drop-down list, which you can use to sort the data or filter the table to hide specific rows.
If you scroll the sheet down so that the header row disappears, the table headers replace the column letters in the worksheet header.
Tables support calculated columns. A single formula in a column is automatically propagated to all cells in the column. (See Chapter 11.)
Tables support structured references. Rather than using cell references, formulas can use table names and column headers. (See Chapter 11.)
The lower-right corner of the lower-right cell contains a small control that you can click and drag to extend the table’s size, either horizontally (add more columns) or vertically (add more rows).
Excel is able to remove duplicate rows automatically.
Selecting rows and columns within the table is simplified.
Most of the time, you’ll create a table from an existing range of data. But Excel also allows you to create a table from an empty range so that you can fill in the details later. The following instructions assume that you already have a range of data that’s suitable for a table.
First, make sure that the range doesn’t contain any completely blank rows or columns.
Activate any cell within the range.
Choose Insert
Tables Table (or press Ctrl+T). Excel responds with its Create Table dialog box, shown in Figure 6.4. Excel tries to guess the range, and whether the table has a header row. Most of the time, it guesses correctly. If not, make your corrections before you click OK.
The range is converted to a table (using the default table style), and the Table Tools Design tab of the Ribbon appears.
Note
Excel may guess the table’s dimensions incorrectly if the table isn’t separated from other information by at least one empty row or column. If Excel guesses incorrectly, just specify the exact range for the table in the Create Table dialog box. Or, click Cancel and rearrange your worksheet such that the table is separated from your other data by at least one blank row or column.
When you create a table, Excel applies the default table style. The actual appearance depends on which document theme is used in the workbook. If you prefer a different look, you can easily change the entire look of the table.
Select any cell in the table and choose Table Tools Design Table Styles. The Ribbon shows one row of styles, but if you click the bottom of the scrollbar to the right, the table styles group expands, as shown in Figure 6.5. The styles are grouped into three categories: Light, Medium, and Dark. Notice that you get a “live” preview as you move your mouse among the styles. When you see one you like, just click to make it permanent.
For a different set of color choices, choose Page Layout Themes Themes to select a different document theme. For more information about themes, refer to Chapter 7.
Tip
If applying table styles isn’t working, it’s probably because the range was already formatted before you converted it to a table. Table formatting doesn’t override normal formatting. To clear the existing background fill colors, select the entire table and choose Home Font Fill Color No Fill. To clear the existing font colors, choose Home Font Font Color Automatic. After you issue these commands, the table styles should work as expected.
If you’d like to create a custom table style, choose Table Tools Design Table Styles New Table Style to display the New Table Quick Style dialog box shown in Figure 6.6. You can customize any or all of the 13 table elements. Select an element from the list, click Format, and specify the formatting for that element. When you’re finished, give the new style a name and click OK. Your custom table style will appear in the Table Styles gallery in the Custom category. Unfortunately, custom table styles are available only in the workbook in which they were created.
Tip
If you would like to make changes to an existing table style, locate it in the Ribbon and right-click. Choose Duplicate from the shortcut menu. Excel displays the Modify Table Quick Style dialog box with all of the settings from the specified table style. Make your changes, give it a new name, and click OK to save it as a custom table style.
This section describes some common actions you’ll take with tables.
Selecting cells in a table works just like selecting cells in a normal range. One difference is when you use the Tab key. Pressing Tab moves to the cell to the right, and when you reach the last column, pressing Tab again moves to the first cell in the next row.
When you move your mouse around in a table, you may notice that the pointer changes shapes. These shapes help you select various parts of the table.
To select an entire column: Move the mouse to the top of a cell in the header row, and the mouse pointer changes to a down-pointing arrow. Click to select the data in the column. Click a second time to select the entire table column (including the header). You can also press Ctrl+Space (once or twice) to select a column.
To select an entire row: Move the mouse to the left of a cell in the first column, and the mouse pointer changes to a right-pointing arrow. Click to select the entire table row. You can also press Shift+Space to select a table row.
To select the entire table: Move the mouse to the upper-left part of the upper-left cell. When the mouse pointer turns into a diagonal arrow, click to select the data area of the table. Click a second time to select the entire table (including the Header Row and the Totals Row). You can also press Ctrl+A (once or twice) to select the entire table.
To add a new column to the end of a table, just active a cell in the column to the right of the table and start entering the data. Excel automatically extends the table horizontally. Similarly, if you enter data in the row below a table, Excel extends the table vertically to include the new row.
Note
An exception to automatically extending tables is when the table is displaying a Totals Row. If you enter data below the Totals Row, the table will not be extended.
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. The Insert shortcut menu command displays additional menu items:
Table Columns To The Left
Table Columns To The Right
Table Rows Above
Table Rows Below
Tip
When the cell pointer is in the bottom-right cell of a table, pressing Tab inserts a new row at the bottom.
When you move your mouse to the resize handle at bottom-right cell of a table, the mouse pointer turns into a diagonal line with two arrow heads. Click and drag down to add more rows to the table. Click and drag to the right to add more columns.
When you insert a new column, the Header Row displays a generic description, such as Column 1, Column 2, and so on. Normally, you’ll want to change these names to more descriptive labels.
To delete a row (or column) in a table, select any cell in the row (or column) to be deleted. If you want to delete multiple rows or columns, select them all. Then right-click and choose Delete Table Rows (or Delete Table Columns).
To move a table to a new location in the same worksheet, move the mouse pointer to any of its borders. When the mouse pointer turns into a cross with four arrows, click and drag the table to its new location.
To move a table to a different worksheet (in the same workbook or in a different workbook):
The Table Tools Design Table Style Options group contains several check boxes that determine whether various elements of the table are displayed, and whether some formatting options are in effect:
Header Row: Toggles the display of the Header Row.
Totals Row: Toggles the display of the Totals Row.
First Column: Toggles special formatting for the first column.
Last Column: Toggles special formatting for the last column.
Banded Rows: Toggles the display of banded (alternating color) rows.
Banded Columns: Toggles the display of banded (alternating color) columns.
The Total Row in a table contains formulas that summarize the information in the columns. Normally, the Total Row isn’t turned on. To display the Total Row, choose Table Tools Design Table Style Options and put a check mark next to Total Row.
By default, the Total Rows display the sum of the values in a column of numbers. In many cases, you’ll want a different type of summary formula. When you select a cell in the Total Row, a drop-down arrow appears, and you can select from a number of other summary formulas (see Figure 6.7):
None: No formula
Average: Displays the average of the numbers in the column
Count: Displays the number of entries in the column (blank cells are not counted)
Count Numbers: Displays the number of numeric values in the column (blank cells, text cells, and error cells are not counted).
Max: Displays the maximum value in the column
Min: Displays the minimum value in the column
Sum: Displays the sum of the values in the column
StdDev: Displays the standard deviation of the values in the column. Standard deviation is a statistical measure of how “spread out” the values are.
Var: Displays the variance of the values in the column. Variance is another statistical measure of how “spread out” the values are.
More Functions: Displays the Insert Function dialog box so that you can select a function that isn’t in the list.
Warning
If you have a formula that refers to a value in the Total Row of a table, the formula returns an error if you hide the Total Row. But if you make the Total Row visible again, the formula works as it should.
Cross-Ref
For more information about formulas, including the use of formulas in a table column, refer to Chapter 11.
If you have a table that contains duplicate items, you may want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task. But Excel 2007 makes it very easy if the data is in a table.
Start by selecting any cell in your table. Then choose Table Tools Design Remove Duplicates. Excel responds with the dialog box shown in Figure 6.8. The dialog box lists all the columns in your table. Place a check mark next to the columns that you want to be included in the duplicate search. Most of the time, you’ll want to select all the columns, which is the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells you how many duplicates it removed.
Warning
It’s important to understand that duplicate values are determined by the value displayed in the cell—not necessarily the value stored in the cell. For example, assume that two cells contain the same date. One of the dates is formatted to display as 5/15/2007, and the other is formatted to display as May 15, 2007. When removing duplicates, Excel considers these dates to be different.
The Header Row of a table contains a drop-down arrow that, when clicked, displays sorting and filtering options (see Figure 6.9).
Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the totals sales made.
To sort a table by a particular column, click the drop-down in the column header and choose one of the sort commands. The exact command varies, depending on the type of data in the column.
You can also select Sort By Color, to sort the rows based on the background or text color of the data. This option is relevant only if you’ve overridden the table style colors with custom colors.
Note
When a column is sorted, the drop-down in the header row displays a different graphic to remind you that the table is sorted by that column.
You can sort on any number of columns. The trick is to sort the least significant column first and then proceed until the most significant column is sorted lasted. For example, in the real estate table, you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted by list price. For this type of sort, first sort by the List Price column, then sort by the Area column, and then sort by the Agent column. Figure 6.10 shows the table sorted in this manner.
Another way of performing a multiple-column sort is to use the Sort dialog box. To display this dialog box, choose Home Editing Sort & Filter Custom Sort. Or, right-click any cell in the table and choose Sort Custom Sort from the shortcut menu.
In the Sort dialog box, use the drop-down lists to specify the first search specifications. Note that the searching is opposite of what I described in the previous paragraph. In this example, you start with Agent. Then, click the Add Level button to insert another set of search controls. In this new set of controls, specify the sort specifications for the Area column. Then, add another level and enter the specifications for the List Price column. Figure 6.11 shows the dialog box after entering the specifications for the three-column sort. This technique produces exactly the same sort as described in the previous paragraph.
Filtering a table refers to displaying only the rows that meet certain conditions. (The other rows are hidden.)
Using the real estate table, assume that you’re only interested in the data for the N. Country area. Click the drop-down in the Area Row Header and remove the check mark from Select All, which unselects everything. Then, place a check mark next to N. County and click OK. The table, shown in Figure 6.12, is now filtered to display only the listings in the N. County area. Notice that some of the row numbers are missing; these rows contain the filtered (hidden) data.
Also notice that the drop-down arrow in the Area column now shows a different graphic—an icon that indicates the column is filtered.
You can filter by multiple values—for example, filter the table to show only N. Country and Central.
You can filter a table using any number of columns. For example, you may want to see only the N. County listings in which the Type is Single Family. Just repeat the operation using the Type column. All tables then display only the rows in which the Area is N. County and the Type is Single Family.
For additional filtering options, select Text Filters (or Number Filters, if the column contains values). The options are fairly self-explanatory, and you have a great deal of flexibility in displaying only the rows that you’re interested in.
In addition, you can right-click a cell and use the Filter command on the shortcut menu. This menu item leads to several additional filtering options.
Why you copy data from a filtered table, only the visible data is copied. In other words, rows that are hidden by filtering don’t get copied. This filtering makes it very easy to copy a subset of a larger table and paste it to another area of your worksheet. Keep in mind that the pasted data is not a table—it’s just a normal range.
To remove filtering for a column, click the drop-down in the Row Header and select Clear Filter. If you’ve filtered using multiple columns, it may be faster to remove all filters by choosing Home Editing Sort & Filter Clear.