Microsoft Office Tutorials and References
In Depth Information
Data Analysis with Pivot Tables
You can also insert a new worksheet with the blank pivot table grid like the
one shown in Figure 9-5 by selecting the Blank PivotTable button in the
Recommended PivotTable dialog box (shown in Figure 9-3) or the Quick
Analysis tool’s options palette (displayed only when Quick Analysis can’t
suggest pivot tables for your data). Just be aware that when you select the Blank
PivotTable button in this dialog box or palette, Excel 2013 does not first open
Create PivotTable dialog box shown in Figure 9-4. If you need to use any of the
options offered in this dialog box in creating your new pivot table, you need to
create the pivot table with the PivotTable command button rather than the
Recommended PivotTables command button on the Insert Tab.
To complete the new pivot table, all you have to do is assign the fields in the
PivotTable Field List task pane to the various parts of the table. You do this
by dragging a field name from the Choose Fields to Add to Report list box and
dropping it in one of the four areas below called drop zones:
✓ FILTERS: This area contains the fields that enable you to page through
the data summaries shown in the actual pivot table by filtering out sets
of data — they act as the filters for the report. For example, if you
designate the Year field from a data list as a report filter, you can display data
summaries in the pivot table for individual years or for all years
represented in the data list.
✓ COLUMNS: This area contains the fields that determine the arrangement
of data shown in the columns of the pivot table.
✓ ROWS: This area contains the fields that determine the arrangement of
data shown in the rows of the pivot table.
✓ VALUES: This area contains the fields that determine which data are
presented in the cells of the pivot table — they are the values that are
summarized in its last column (totaled by default).
To understand how these various zones relate to a pivot table, look at the
completed pivot table shown in Figure 9-5.
For this pivot table, I assigned the Gender field from the data list (a field that
contains F (for female) or M (for male) to indicate the employee’s gender in
the FILTERS drop zone. I also assigned the Dept field (that contains the names
of the various departments in the company) to the COLUMNS drop zone, the
Location field (that contains the names of the various cities with corporate
offices) to the ROWS drop zone, and the Salary field to the VALUES drop zone.
As a result, this pivot table now displays the sum of the salaries for both the
male and female employees in each department (across the columns) and then
presents these sums by their corporate location (in each row).
As soon as you add fields to a new pivot table (or select the cell of an existing
table in a worksheet), Excel selects the Analyze tab of the PivotTable Tools
contextual tab that automatically appears in the Ribbon. Among the many