Analyzing Microsoft Excel 2010 Data with PivotTable Reports
- 8/31/2010
- Introducing PivotTables
- Creating a PivotTable
- Rearranging PivotTable Fields
- Refreshing a PivotTable
- Changing the Numeric Format of PivotTable Data
- Choosing Report Layout Options
- Formatting a PivotTable
- Displaying Totals and Subtotals
- Sorting PivotTable Fields
- Filtering PivotTable Fields
- Changing PivotTable Calculations
- Grouping and Ungrouping Data
- Displaying the Details Behind a Data Value
- Creating PivotCharts
Grouping and Ungrouping Data
PivotTables group inner field items under each outer field heading and, if requested, create subtotals for each group of inner field items. You might find it convenient to group items in additional ways—for example, to collect monthly items into quarterly groups or sets of numbers into larger numeric categories. Excel provides several options for grouping items.
Creating Ad Hoc Item Groupings
Suppose that after looking at Figure 23-3 you decide you’d like to see the domestic and international sales figures grouped into a category called Retail. To create this group, select the Domestic and International items anywhere in the table. Then click the Options tab under PivotTable Tools, and click Group Selection in the Group group. Excel creates a new heading called Group1:
){kind=link}
Now you can rename Group1 by typing over any instance of it.
Grouping Items in Date or Time Ranges
Figure 23-24 shows a PivotTable that summarizes daily transactions by payee. As you can see, the data in this table is extremely sparse. Most intersections between a day item and a payee item are blank.
)
Figure 23-24 To make the data in this table more meaningful, you can group the date field.
You’ll find the Transactions.xlsx file with the other examples on the companion Web site.
To make this kind of table more meaningful, you can group the date field. To do this, select an item in the field. Then click the Options tab under PivotTable Tools, and click Group Field. Excel responds by displaying the Grouping dialog box, shown in Figure 23-25.
){kind=link}
Figure 23-25 Excel gives you lots of ways to group by date.
Excel gives you a great deal of flexibility in the way your date and time fields are grouped. In the By list, you can choose any common time interval, from seconds to years, and if the standard intervals don’t meet your needs, you can select an arbitrary number of days. You can also create two or more groupings at the same time (hold down Ctrl while you select); the results of grouping by both Quarter and Month are shown in Figure 23-26.
)
Figure 23-26 In this table, daily data is grouped by months and then by quarters.