Microsoft Office Tutorials and References
In Depth Information
Modifying Pivot Tables
zone and the Location field from the ROWS drop zone to the COLUMNS
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.
You can switch column and row fields by dragging their labels to their new
locations directly in the pivot table itself. Before you can do that, however,
you must select the Classic PivotTable Layout (Enables Dragging of Fields in
the Grid) check box on the Display tab of the PivotTable Options dialog box.
To open this dialog box, you select the Options item on the Pivot Table
button’s drop-down menu. This button is located at the very beginning of the
Analyze tab beneath the PivotTable Tools contextual tab.
Modifying the table’s summary function
By default, Excel uses the good old SUM function to create subtotals and
grand totals for the numeric field(s) that you assign as the Data Items in the
Some pivot tables, however, require the use of another summary function,
such as AVERAGE or COUNT. To change the summary function that Excel uses,
simply click the Sum Of field label that’s located at the cell intersection of the
first column field and row field in a pivot table. Next, click the Field Settings
command button on the Analyze tab to open the Value Field Settings dialog
box for that field, similar to the one shown in Figure 9-9.
After you open the Value Field Settings dialog box, you can change its
summary function from the default Sum to any of the following functions by
selecting it in the Summarize Value Field By list box: