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.
Search JabSto ::




Custom Search