Microsoft Office Tutorials and References
In Depth Information
Chapter 26: Pivot Tables
When you have a large database and you want to discover trends and patterns at a
glance, you can use PivotTable reports to help you. Pivot tables allow you to:
n Summarize the data contained in large tables into a compact layout.
n Find relationships within the data that are hard to evaluate because of a vast
amount of data.
n Organize the data into a format that can be charted easily.
First you have to decide what questions you want the data to answer. To con-
struct a pivot table you need to identify these two elements in your data:
1. Which parameters or data fields are the variables you want to summarize;
2. What column fields are the variables that will
the data summary.
Certain constraints exist:
In the first row of the data, each one of the columns should have a unique header
There should be no empty rows or columns within the range of data used for the
report. Each column should contain only one type of data: text in one column
and numeric values in a separate column.
This section will start with a simple example using the database employed previously.
See Figure 26.1 for the database. In this example, you will want to calculate the
average age by gender and job.
To start the PivotTable, select any cell (or range of cells) in the database and click
on the PivotTable icon in the Insert ribbon. Click OK on the Create PivotTable menu.
See Figure 26.2.
In Excel 2010, the layout and feel is slightly different than in previous versions of
Excel, but the functionality is the same. Once you activate the PivotTable, it will
appear as in Figure 26.3. I dragged the PivotTable field list closer to the PivotTable