Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

One of the more 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 18-17 shows a pivot table created from an employee list in columns A:C, which has the following fields: Employee, Location, and Sex. The pivot table, in columns E:H shows the number of employees in each of six states, cross-tabulated by sex.

Figure 18-17: A pivot table before creating groups of states.

The goal is to create two groups of states: Western Region (Arizona, California, and Washington), and Eastern Region (Massachusetts, New York, and Pennsylvania). 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. I repeated the operation to create the second group. Then I replaced the default group names (Group 1 and Group 2) with more meaningful names (Eastern Region and Western Region). Figure 18-18 shows the result of the grouping.

Figure 18-18: A pivot table with two groups and subtotals for the groups.

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

Viewing Grouped Data

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 in the PivotTable Tools Design tab of the Ribbon. There are no rules for these options. 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.

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

Figure 18-19: Pivot tables with options for subtotals and grand totals.

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 18-20 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, 2005, and December 31, 2006. The goal is to summarize the sales information by month.

Figure 18-20: You can use a pivot table to summarize the sales data by month.

Figure 18-21 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.

Figure 18-21: The pivot table, before grouping by month.

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 18-22.

Figure 18-22: 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. Click OK. The Date items in the pivot table are grouped by years and by months, as shown in Figure 18-23.

Figure 18-23: The pivot table, after grouping by month and year.

Figure 18-24 shows another view of the data, grouped by quarter and by year.

Figure 18-24: This pivot table shows sales by quarter and by year.

GROUPING BY TIME

Figure 18-25 shows a set of data in columns A:B. Each row is a reading from an instrument, taken at one-minute intervals throughout an entire day. The table has 1,440 rows, each representing one minute. The pivot table summarizes the data by hour.

Figure 18-25: This pivot table is grouped by Hours.

Following are the settings I used for this pivot table:

• The values area has three instances of the Reading field. I used the Data Field Setting dialog box (Summarize By tab) to summarize the first instance by Average, the second instance by Min, and the third instance by Max.

• The Time field is in the Row Labels section, and I used the Grouping dialog box to group by Hours.