Excel 2010 Bible

Chapter 10: Introducing Formulas and Functions

215

On the CD
This workbook is available on the companion CD-ROM. It is named table formulas.xlsx.

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:

1. Activate any cell in the table.


2. Place a check mark next to Table Tools ➪ Design ➪ Table Style Options ➪ Total
   Row.


3. Activate a cell in the Total Row and use the drop-down list to select the type of
   summary formula to use (see Figure 10.11). For example, to calculate the sum of
   the Actual column, select SUM from the drop-down list in cell D15. Excel creates this
   formula:
   =SUBTOTAL(109,[Actual])

For the SUBTOTAL function, 109 is an enumerated argument that represents SUM. 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. (I discuss this further in the

upcoming section, “Referencing data in a table.”)

FIGURE 10.11

A drop-down list enables you to select a summary formula for a table column.