Microsoft Office Tutorials and References

In Depth Information

**Adding Totals and Formulas to a Table**

Although the result is functionally the same as if you had clicked the Sum button, the

formula itself uses the SUBTOTAL function instead. You can add a summary formula to any cell

in the Total row or change the results for an existing formula by selecting a cell and clicking

the arrow just to its right, as shown here:

As you can see, we added a formula at the bottom of column B that displays the average

number of orders per day, and we’re about to change the formula beneath column C so

that it shows an average instead of a sum. If you look in the formula bar, you can see the

syntax of the SUBTOTAL function used for these calculations.

What if you want to create a calculated column that displays totals, averages, or other

summaries on a per-row basis? Excel can do that automatically. In the previous example,

click any cell in any column to the right of the table range and begin entering a formula.

In this worksheet, the number of orders for each day is in column B and the total sales for

each day is in column C, so we can click in D2, type an equal sign, click C2, type a slash

(/), and click B2. As soon as we press Enter, Excel creates a new column using the current

table format and copies the formula we just typed to every cell in that column, as shown in

Figure 12-5.

INSIDE OUT
Take control of calculated columns

If you create a formula to the right of the current table and you don’t want it to be

copied to other cells in the column, use the options on the AutoCorrect menu to

immediately undo the calculated column. To prevent Excel from automatically adding

new rows or columns to a table, click File, click Options, and then click AutoCorrect

Options on the Proofing tab. On the AutoFormat As You Type tab, under the Apply As

You Work heading, clear Include New Rows And Columns In Table.