Part V: Analyzing Data with Excel

FIGURE 34.19

This pivot table uses three Report Filters.

Learning More

The examples in this chapter should give you an appreciation for the power and flexibility of Excel pivot tables. The next chapter digs a bit deeper and covers some advanced features — with lots of examples.

714

CHAPTER

Analyzing Data with

Pivot Tables

715

Part V: Analyzing Data with Excel

Here are the settings I used for this pivot table:

•The Sex field is used for the Column Labels.

•The Location field is used for the Row Labels.

•Location is used for the Values and is summarized by Count.

•The pivot table has the field headers turned off (by choosing PivotTable Tools OptionsShow Show Field headers).

FIGURE 35.1

This table doesn’t have any numeric fields, but you can use it to generate a pivot table, shown next to the table.

Note

The Employee field is not used. This example uses the Location field for the Values section, but you can actually use any of the three fields because the pivot table is displaying a count. n

Figure 35.2 shows the pivot table after making some additional changes:

•I added a second instance of the Location field to the Values section. To display percentages, I right-clicked a value in that column, and chose Show Values As Percent of Column Total.

•I changed the field names in the pivot table to Count and Pct.

•I selected a Pivot Table Style that makes it easier to distinguish the columns.

716

Chapter 35: Analyzing Data with Pivot Tables

FIGURE 35.2

The pivot table, after making a few changes.

Grouping Pivot Table Items

One of the most useful features of a pivot table is the ability to combine items into groups. You can group items that appear as Row Labels or Column Labels. Excel offers two ways to group items:

•Manually: After creating the pivot table, select the items to be grouped and then choose PivotTable Tools Options Group Group Selection. Or, you can right-click and choose Group from the shortcut menu.

•Automatically: If the items are numeric (or dates), use the Grouping dialog box to specify how you would like to group the items. Select any item in the Row Labels or Column Labels and then choose PivotTable Tools Options Group Group Selection. Or, you can right-click and choose Group from the shortcut menu. In either case, Excel displays its Grouping dialog box.

A manual grouping example

Figure 35.3 shows the pivot table example from the previous sections, with two groups created from the Row Labels. To create the first group, I held the Ctrl key while I selected Arizona, California, and Washington. Then I right-clicked and chose Group from the shortcut menu. Excel created a second group automatically. Then I replaced the default group names (Group 1 and Group 2) with more meaningful names (Western Region and Eastern Region).

You can create any number of groups, and even create groups of groups.

Excel provides a number of options for displaying a pivot table, and you may want to experiment with these options when you use groups. These commands are on the PivotTable Tools Design tab of the Ribbon. There are no rules for choosing a particular option. The key is to try a few and see which makes your pivot table look the best. In addition, try various PivotTable Styles, with options for banded rows or banded columns. Often, the style that you choose can greatly enhance readability.

717

Part V: Analyzing Data with Excel

FIGURE 35.3

A pivot table with two groups.

Figure 35.4 shows pivot tables using various options for displaying subtotals, grand totals, and styles.

FIGURE 35.4

Pivot tables with options for subtotals and grand totals.

718

Chapter 35: Analyzing Data with Pivot Tables

Automatic grouping examples

When a field contains numbers, dates, or times, Excel can create groups automatically. The two examples in this section demonstrate automatic grouping.

Grouping by date

Figure 35.5 shows a portion of a simple table with two fields: Date and Sales. This table has 730 rows and covers the dates between January 1, 2008 and December 31, 2009. The goal is to summarize the sales information by month.

FIGURE 35.5

You can use a pivot table to summarize the sales data by month.

On the CD

A workbook demonstrating how to group pivot table items by date is available on the companion CD-ROM. The file is named sales by date.xlsx.

Figure 35.6 shows part of a pivot table created from the data. The Date field is in the Row Labels section, and the Sales field is in the Values section. Not surprisingly, the pivot table looks exactly like the input data because the dates have not been grouped.

To group the items by month, select any date and choose PivotTable Tools Options Group Group Field (or, right-click and choose Group from the shortcut menu). You see the Grouping dialog box, shown in Figure 35.7. Excel supplies values for the Starting At and Ending At fields. The values cover the entire range of data, and you can change them if you like.

719

Part V: Analyzing Data with Excel

FIGURE 35.6

The pivot table, before grouping by month.

FIGURE 35.7

Use the Grouping dialog box to group pivot table items by dates.

In the By list box, select Months and Years and verify that the starting and ending dates are correct for your data. Click OK. The Date items in the pivot table are grouped by years and by months, as shown in Figure 35.8.

Note

If you select only Months in the By list box in the Grouping dialog box, months in different years combine together. For example, the January item would display sales for both 2008 and 2009. n

720

Chapter 35: Analyzing Data with Pivot Tables

FIGURE 35.8

The pivot table, after grouping by month and year.

Figure 35.9 shows another view of the data, grouped by quarter and by year.

FIGURE 35.9

This pivot table shows sales by quarter and by year.

721