Page 20 of 25PREVNEXT

# PivotTable III: Show off your PivotTable skills

###### Quick reference card

The report with subtotals.
The report with automatic subtotals turned off.

Now the bonus percentage for each quarter is listed. But notice that Peacock's bonus percentage total for the four quarters in cell D9 is 12%, even though she didn't get a 12 percent bonus for each quarter.

Excel added up the order amounts in column C for each salesperson, row by row, because automatic subtotals are turned on by default (whether the report is formatted or not). For example, Peacock's subtotal for the year is listed in cell C9: \$225,764.

Excel also ran the calculated field formula row by row. When Excel came to the subtotal row, where the total for the four quarters is over \$50,000, the formula worked in that row the same as for the previous rows. Remember, the formula said that if over \$50,000, give a 12 percent bonus, which on this row is mathematically correct, but not the right answer.

The solution? Turn off the automatic subtotals, which you'll see how to do in the practice session.

Page 20 of 25PREVNEXT