Microsoft Office Excel 2007 Inside Out
By Mark Dodge and Craig Stinson

Mark Dodge is a former senior technical writer for the Microsoft Office User Assistance group, and is the coauthor of four editions of Running Microsoft Excel. He was also a technical editor for over a dozen books on Microsoft applications. Mark has been honored with six awards from the Society for Technical Communication.

Craig Stinson has been an industry journalist since 1981, serving as a contributing editor of PC Magazine and author of the several editions of the best-selling Running Microsoft Windows®. In addition to being a coauthor on Running Microsoft Excel, he is the coauthor of Running Microsoft Windows NT® Workstation, Version 4. He has also written music reviews for such well-known publications as Billboard, the Boston Globe, and the Christian Science Monitor.

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

---

---

• New table management features in Excel 2007

• How to organize a table

• Creating a table

• Selecting rows and columns within a table

• Adding totals to a table

---

This article introduces a whole new set of features for managing information in tables that are included in Microsoft Office Excel 2007. 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.

New table management features in Excel 2007

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 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 1), Excel automatically replicates the calculation formula throughout the column.

  

  Figure 1  Structured referencing, exemplified by the formula in E2, makes calculations easier to understand and less prone to error.
• 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.

 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.

 Top of Page

How to organize a table

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” below.)

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 will generate one for you 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.

 Top of Page

Creating a table

After you have some data in a worksheet range, you can designate that range as a table by selecting any cell within it and pressing Ctrl+T or Ctrl+L. That’s the easy way. If you want to work a little harder, you can click Table in the Tables group on the Insert tab. Either way, start by selecting a single cell anywhere in the table range before issuing the command. Excel will figure out the dimensions of the table for you and ask for confirmation in the Create Table dialog box:

Unless the program has made some kind of mistake, you can click OK to create your table. If you select more than one cell but less than the entire range before pressing Ctrl+T, Excel will try to create a table out of the specific cells you selected.

 Note    Many new features of Excel tables do not work if your workbook is opened in Compatibility mode. You must convert a workbook you have saved with the type Excel 97–2003 Workbook to an Excel 2007 workbook in order to get the new functionality.

Overwriting default headers

Notice that, in addition to recognizing the size of the table, Excel figures out whether the top row of your range is a header row—a row of column labels. If your range does not include such a row or if for some reason you choose to clear the My Table Has Headers check box in the Create Table dialog box, Excel will create a header row for you using labels such as Column 1, Column 2, and so on. Default headers like these are both ugly and pointless; it’s far better to set up your own descriptive headers before creating the table. But if you omit this step, you can always override the defaults later by selecting the header cells and typing over them, just as if they were ordinary worksheet cells. (They’re not quite ordinary data, actually; Excel won’t let you delete them, and if you try to clear a header, you’ll just get the default back.)

Turning a table back into an ordinary range

If the need arises to turn a table back into an ordinary worksheet range, select any cell or block of cells within the table. Then click Convert To Range in the Tools group on the Design tab. Click Yes to answer the confirmation prompt. Note that after you change a table into a regular range, the formatting turns into regular cell formatting. This can cause unexpected behavior if you ever turn the range back into a table.

 Tip   An easy way to tell whether a range is a table is to select a cell in the range and look at the Ribbon. If you see a Table Tools tab, then the current list has been converted to a table.

Naming a table

When you designate a range as a table, Excel assigns a name to that table and displays the name in the Properties group on the Design tab:

As this example shows, Excel uses default names (Table1, Table2, and so on) unless you supply your own names. Does the name matter? Perhaps. Formulas that take advantage of structured referencing use the table name, and a descriptive name serves the purpose of self-documentation better than a default name. In the following formula, for example:

=SUM(Scores[Math])

the word Scores is the table’s name. (The formula sums the values from the Math column of the Scores table.)

Giving a meaningful name to the table is particularly useful when you have multiple tables on a single worksheet and have formulas that refer to the tables. By using names for the tables, you can instantly tell when looking at a formula which table it is referencing.

It’s definitely worthwhile to assign an intelligible name to your table if you think you might at some point record or write a macro that references the table. That way your macro code will be easier to understand (easier for you and easier for anyone else who sees your code). Moreover, if you record a macro that references Table1 and you subsequently name the table SurveyData, your macro will no longer perform as expected and will cause you considerable vexation. It’s best to form the habit of naming objects when you create them.

To change a table’s current name (default or otherwise), select a cell within the table, and click the Design tab. Then type in the Table Name box in the Properties group.

Expanding a table

To add a new row to the end of a table, go to the bottom-right cell of the table (ignoring the total row, if there is one), and press Tab. Excel will extend the table for you, no questions asked, copying all formatting and formulas in the process. When you get to the last column in the new row, press Tab to create yet another new row. Thus, after you have created the stub of a table, you can expand it downward by simply typing in the usual way and pressing Tab between cells (or at any rate at the end of each row).

Note that pressing Tab creates a new table row above the total row, if your table has a total row. The total row simply moves down one row to accommodate your new data, and Excel updates the formulas appropriately. (For more about the total row, see “Adding Totals to a Table” below.) If you don’t have a total row in your table, you can also extend the table by simply typing in the blank row below the bottom row of the table. Using Tab to extend the table works whether you have a total row or not.

Automatic expansion works for columns as well as rows. If you type in any row of the column directly to the right of a table, Excel expands the table to include the new column. If the new data is a formula, the formula is replicated throughout the column.

If you don’t want the table to automatically expand or automatically fill columns with formulas, you can turn off the option. Click the Microsoft Office Button, and then click Excel Options. Select the Proofing category, and click AutoCorrect Options. In the AutoCorrect dialog box, shown in Figure 2, click the AutoFormat As You Type tab. Clear the Include New Rows And Columns In Table check box to prevent Excel from expanding the table, and clear the Fill Formulas In Tables To Create Calculated Columns check box to prevent Excel from filling entire columns with identical formulas.

If you’re not currently displaying a total row with your table, you’ll find a minuscule handle in the lower-right corner of the cell occupying the lower-right corner of your table. This handle gives you yet another way to expand your table. Usually, it’s easier just to add data and let Excel expand the table. But if you want to add several new rows or columns all at once, the handle is a good way to do it.

 Top of Page

Selecting rows and columns within a table

Excel makes it easy to select rows and columns within a table. If you rest the pointer on the left edge of the first cell in a row, the pointer changes to a solid arrow. Click once, and you’ve selected the row. If your table happens to begin in column A of the worksheet, be sure you rest the pointer inside the first cell, rather than on the worksheet frame. On the frame, the pointer also changes to a solid arrow, but clicking here will select the entire worksheet row.

To select a column, rest the pointer near the top of the column’s heading, and then click. Clicking once selects the column’s data, excluding the header and the total (if you have displayed the total row). Clicking a second time selects the entire column—header, data, and total.

To select the entire table, rest the pointer on the upper-left corner of the first column’s header. When you see the pointer turn southeast, click. Click once for the data only or twice for everything—data, headers, and totals.

Selecting with the keyboard is even easier, particularly with a large table when the top and left edges are out of sight. Pressing Shift+Spacebar selects the current row, regardless of which cell is selected. Pressing Ctrl+Spacebar selects the current column’s data, omitting the header and total. Pressing Shift+Ctrl+Spacebar or Ctrl+A selects all the table’s data.

Multiple key presses expand the selection predictably: Pressing Shift+Spacebar twice selects the entire current worksheet row. Pressing Ctrl+Spacebar twice selects the current table column, with the header and total. Pressing Ctrl+Spacebar three times selects the entire current worksheet column. Pressing Shift+Ctrl+Spacebar or Ctrl+A twice gets the entire table, headers, and totals included. Pressing that combination a third time selects all gazillion cells of the worksheet.

 Top of Page

Adding totals to a table

To add a total row to your table, select any cell within the table, and then select the Total Row check box in the Table Style Options group on the Designer tab. You can toggle the row on or off by selecting or clearing this check box. Figure 3 shows an example of a total row.

By default, the total row applies the SUBTOTAL function, with a Function_num argument of 109, to the rightmost column of the table. (Using 109 in the Function_num argument creates a sum that ignores all rows hidden by filters.) That generates a sum in the lower-right corner—which might not be what you want. In Figure 3, for example, it would make more sense to show an average in that cell than a sum. It would also be useful to calculate averages in the Critical Reading, Writing, and Math columns, as well as the Total column. All that is quite easy to do. When you click the small arrow at the right edge of a total row cell, a list of alternative functions appears:

You can make the same list appear in any other total row cell (not just the rightmost) by selecting the cell and clicking the arrow that appears. You can also type directly over any of the total row cells. Here’s how you might make the total row look if you wanted to replace the sums with averages:

Here are a few more points to note about the total row:

• Unlike the total row in an Excel 2003 list, the Excel 2007 total row does not limit you to the commonplace aggregation functions. With the help of the More Functions command in the list, you can create any kind of formulas you want.
• Because the choices in the list—AVERAGE, COUNT, COUNT NUMBERS, and so on—generate formulas based on the SUBTOTAL function (using arguments in the 101–111 range), they ignore rows that are hidden by filters. If you want to aggregate based on all rows except those you manually hide, subtract 100 from the first argument function. For example, change SUBTOTAL(101,column) to SUBTOTAL(1,column). If you want aggregate calculations based on all rows, ignoring the column filter settings, change the formulas to standard aggregate functions. For example, substitute SUM(column) for SUBTOTAL(109,column).
• After you’ve customized the formulas in the total row, turning the total row off and then back on retains your customized formulas. If you frequently toggle the total row off and on, consider putting the command on your Quick Access Toolbar. (Click the Design tab under Table Tools, right-click the Total Row check box, and select Add To Quick Access Toolbar.)

 Note    You can also add subtotals to your table. Excel still includes the Subtotal command to insert subtotal rows into the middle of a table, but you can achieve the same result more easily in almost all cases by using a PivotTable.

 Top of Page