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 21-3 shows an example of a total row.

Figure 21-3: The total row uses the SUBTOTAL function to perform summary calculations.

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 21-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 SUBTOTAE 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 SUBTOTAE(101, column) to SUBTOTAE(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. See Chapter 22, "Analyzing Data with PivotTable Reports," for information about working with a PivotTable report.