Exploring Office 2007: Experimenting with Excel's Pivot Tables and Charts A Pain-Free Guide to Creating Your First Pivot Table Helen Bradley
One of Excel's most powerful features is its Pivot Table tool. However, unfortunately it's a tool that's all too often overlooked because it appears to be complicated to use. This is a pity because Pivot tables let you summarize vast quantities of data in a meaningful way with only a minute or two of effort.
Plus, Pivot Tables are adjustable so you can easily rearrange and filter them to answer a range of questions that would take time and an in-depth knowledge of Excel functions to perform any other way. Luckily, pivot tables in Excel 2007 are easier to use than in past versions, so if you've never used pivot tables before now is a great time to get working with them. Let's get started by choosing the data for your first pivot table.
»
Choose Your Data
Pivot tables work best with certain sets of data. Typically the data that you'll use is data that has repetitive entries such as all the invoices that your business has sent out during the last twelve months. You can expect that there will be multiple entries for certain purchasers and, if a sales representative is associated with each invoice, then sales representatives will have multiple invoices associated with them.
A pivot table allows you to analyze the invoices and to answer questions such as "How many invoices can be attributed to a particular sales person?" or "What was the total or average amount of the invoices that they were responsible for?"
»
Create a Pivot Table
To create a pivot table based on this data, select the data including the column headings and choose the Insert tab on the Ribbon. Click the Pivot Table button to open the Create PivotTable dialog. Select the Select a Table or Range option and check that your data range is correctly identified.
Select the New Worksheet option and click Ok. This places the pivot table on a new worksheet in your workbook.
Notice that the PivotTable Field List appears at the right side of the dialog — this is new to Excel 2007. At the foot of the PivotTable Field List are four boxes.
The Report Filter is a field that the entire pivot table can be filtered by. The Row Labels area contains fields that are labels for rows, the Column Labels area contains fields that appear in columns and the S Values area holds fields that have a calculation made on them.
By default, numeric fields are added to the S Values area and are summed, and nonnumeric fields appear in the Row Labels area. If a non-numeric field is added to the S Values area it has the Count function applied to it.
Click a checkbox such as that for the Customer name and it will be added to the pivot table report although not necessarily in the place that you want it to appear. You can drag a field name from one box to another to change its positioning in the table.
Continue to enable the checkboxes for each field to include in the pivot table. Alternately you can drag and drop a fieldname into the desired area at the foot of PivotTable Field List pane.
When a numeric field such as Invoice Amount is selected, it is summed automatically. As in our example, where the data is also categorized by half year, we can add this to the Report Filter area so that the pivot table can be filtered to show only the selected half-year results, or it can show the results for the full year.
To format data in the pivot table, click in the pivot table and apply a pivot table style from the PivotTable Styles dropdown list in the Design tab on the Ribbon. To apply a number format, right click a value, and select Number Format and then apply a number format from the dialog. All values that relate to that field will be formatted at the one time.
In addition to the PivotTable Field List, the PivotTable Tools portion of the ribbon is also new to Excel 2007. It appears only when the pivot table is selected includes the Options and Design tabs which you can use to format and work with your pivot table. For example, choose Pivot Table Tools > Design and select the banded rows and your colors will alternate through your table, making it easier to read data across a large table.
