Part II: Working with Formulas and Functions
If you’d like to calculate the total projected and total actual sales, you don’t even need to
write a formula. Simply click a button to add a row of summary formulas to the table:
- Activate any cell in the table.
- Place a check mark next to Table Tools Design ➪ Table Style Options ➪ Total Row.
Excel adds a total row to the table and displays the sum of each numeric column.
- To change the type of summary formula, activate a cell in the total row and use
the drop-down list to change the type of summary formula to use (see Figure
9.10). For example, to calculate the average of the Actual column, select AVERAGE
from the drop-down list in cell D15. Excel creates this formula:
=SUBTOTAL(101,[Actual])
FIGURE 9.10
A drop-down list enables you to select a summary formula for a table column.
For the SUBTOTAL function, 101 is an enumerated argument that represents AVERAGE. The
second argument for the SUBTOTAL function is the column name in square brackets. Using
the column name within brackets creates “structured” references within a table. (We dis-
cuss this further in the upcoming section “Referencing data in a table.”)
You can toggle the total row display via Table Tools Design ➪ Table Style Options ➪ Total Row. If you turn it off, the
summary options you selected will be displayed again when you turn it back on.
