Page 267 - Excel Workbook for Dummies

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

7. Click Data Area on the drop-down list at the bottom of the PivotTable Field List
dialog box, and then click the Salary field before you select the Add To button.
Excel adds the Salary field for the data items in the pivot table and Sum of Salary
appears in cell A3, indicating the SUM function is being used.
8. Make the Gender and Profit Sharing fields the two page fields for this pivot table.
You can do this either by dragging the Gender and Profit Sharing fields from the
PivotTable Field List dialog box and dropping them on the Drop Page Fields Here
cells in the worksheet, or by selecting Page Area in the drop-down list at the
bottom of this dialog box and then selecting these fields in succession followed
by the Add To button.
9. Apply the Table 3 style to your completed pivot table.
Click the Format Report button on the PivotTable toolbar to open the AutoFormat
dialog box and then select the thumbnail of the Table 3 style before you select OK.
10. Rename the Sheet2 worksheet to Pivot Table, select cell A1 in this sheet, and
then save your changes in a new workbook named Solved19-2.xls in your
Chapter 19 folder in the My Practice Spreadsheets folder. Leave the workbook
file open for Exercise 19-3.

Modifying the Pivot Table

The fun just begins with the creation of the basic pivot table. After that, you refine
its look by formatting the table with the AutoFormat styles as you did in the previous
exercise, or by selecting different portions of the table and individually formatting
them. You can also show and hide new levels of detail in the table. Perhaps most
important, you can modify the structure of the table by pivoting its row, column, and
paging fields. Finally, you can change the table’s basic summary function as well as
add your own calculated fields to the pivot table.

Modifying the table formatting

The one thing that stands out like a sore thumb in the pivot tables you create is their
lack of basic formatting. When Excel creates a new pivot table, it does not pick any
formatting from the original data source. This means that you have to manually apply
whatever number formats and other kinds of table formatting you want to apply.

Fortunately, as you’ve seen, Excel makes it easy to format the overall table itself with
the AutoFormat styles available by clicking the Format Report button on the PivotTable
toolbar. In addition, you can format individual parts of the pivot table by selecting them
and then applying particular formatting to them.

Try It

Exercise 19-3: Modifying the Formatting in a Pivot Table
Use the Solved19-2.xls workbook you created in the previous exercise to practice for-
matting individual parts of the basic pivot table as well as hiding and showing different
levels of detail: