Page 271 - Excel Workbook for Dummies

27_798452 ch19.qxp 3/13/06 7:42 PM Page 254
254 Part V: Doing Data Analysis

2. Change the summary function from AVERAGE to MAX so that the table shows
you the highest salaries for each location and department in the company.
3. Restore SUM as the summary function in the table and then, in the PivotTable
toolbar, select Formulas➪Calculated Field on the PivotTable drop-down button
to open the Insert Calculated Field dialog box.
Here, you will define the parameters for a Bonus field that calculates bonuses as
2.5% of the employee’s salary.
4. Type Bonus in the Name text box in the Insert Calculated Field dialog box.
5. Select the Formula text box, delete the 0 (zero) after the = (equal to) sign (which
you want to retain), and then click Salary in the Fields list box followed by the
Insert Field button.
The Formula text box should now read
=Salary
6. Type * (asterisk) followed by 0.025 in the Formula text box.
The Formula text box should now contain the complete formula
=Salary*0.025
7. Select the Add button to add the new Bonus field to end of the fields listed in the
Fields list box and then select OK.
As soon as the Insert Calculated Field dialog box closes, you see that Excel has
added your Bonus field (as Sum of Bonus) as a data item to the body of the pivot
table. You also note that Bonus appears at the very bottom of the list box in the
PivotTable Field List dialog box.
8. Position the cell cursor in cell A1 of the Pivot Table worksheet and then save
your changes to the pivot table in a new workbook file named Solved19-5.xls
in your Chapter 19 folder in the My Practice Spreadsheets folder. Close the
workbook file.

Creating Pivot Charts

You can spice up your data summaries quite a bit by generating a pivot chart along
with a supporting pivot table. To do this, you follow the same procedure as you do
when creating a sole pivot table except that you click the PivotChart Report (with
PivotTable Report) option button in the PivotTable and PivotChart Wizard - Step 1 of
1 dialog box.
When creating a new pivot chart with a pivot table, Excel always places the pivot
chart on a new chart sheet regardless of whether you place the associated pivot table
on a new worksheet or elect to place it somewhere on the worksheet that’s current
when you open the PivotTable and PivotChart wizard. Figure 19-2 shows you how a
typical pivot chart appears in its own chart sheet right after you click the Finish
button in the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box.

As with the pivot table, you can assign fields to the pivot chart by dragging them to
the designated areas in the chart (Drop Page Fields Here, Drop Data Items Here, Drop
Series Fields Here, or Drop Category Fields Here). Alternatively, you can click the field
name in the Task Pane Field List, select the name of the chart area to which to assign
the field in the drop-down list box at the bottom of the Task pane, and finally click the
Add To button to its immediate left.