Page 268 - Excel Workbook for Dummies

27_798452 ch19.qxp 3/13/06 7:42 PM Page 251
Chapter 19: Generating Pivot Tables 251

1. Click the Format Report button on the PivotTable toolbar to open the AutoFormat
dialog box and then select the thumbnail of the PivotTable Classic style before
you select OK.
2. On the PivotTable toolbar, choose Select➪Entire Table on the PivotTable’s
drop-down button’s list to select all the cells in the pivot table (including the
page fields).
3. Choose Select➪Label on the PivotTable’s drop-down button’s list to select all the
cells with labels in the pivot table.
4. Click the Bold button on the Formatting toolbar to display the titles in the pivot
table in boldface type.
5. On the Formatting toolbar, click the Light Green color square on the Fill Color
button’s drop-down list to display the cells containing the pivot table titles with
a light-green colored background.
6. Choose Select➪Data on the PivotTable’s drop-down button’s list to select only
the cells with data items in the pivot table.
7. Click the Currency Style button on the Formatting toolbar and then the Decrease
Decimal button twice. The salary totals in the pivot table now appear in the
Currency number format with no decimal places.

8. On the Formatting toolbar, click the Light Yellow color square on the Fill Color
button’s drop-down list to display the cells containing the pivot table data items
with a light-yellow colored background.
9. Click cell A1 in the Pivot Table worksheet and then save your changes in a new
workbook named Solved19-3.xls in your Chapter 19 folder in the My Practice
Spreadsheets folder. Leave the workbook file open for Exercise 19-4.

Pivoting the table’s fields

As the name “pivot” implies, the fun of pivot tables is being able to rotate the data
fields used as the rows and columns of tables as well as to change what fields are
used on the fly. For example, suppose in the Employee Data List pivot table that, after
making the Dept field the pivot table’s sole column field and the Location field the
sole row field, you now want to see what the table looks like with the Location field as
the column field and the Dept field as row field.

No problem: All you have to do is drag the Dept field label from the top row of the
table and drop it in the first column and then drag the Location field label from the
first column and drop it on the first row. Voilá! Excel rearranges the totaled salaries so
that the rows of the pivot table show the departmental grand totals and the columns
now show the location grand totals.

Try It

Exercise 19-4: Modifying the Structure of a Pivot Table
Use the Solved19-3.xls workbook you created in the previous exercise to practice pivot-
ing the row and columns of the basic pivot table as well as modifying the page fields:

1. Exchange the position of the Dept and Location fields in the pivot table by pivot-
ing their fields: Drag the Dept column field and then drop it on top of the Location
row field. Then, drag the Location row field up and drop it on cell B4 to the imme-
diate right of the Sum of Salary cell (A4) to make it into the column field.