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 22-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:

image from book

Now you can rename Group1 by simply typing over any instance of it.

Grouping Items in Date or Time Ranges

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

image from book
Figure 22-22: To make the data in this table more meaningful, you can group the date field.

On the CD You'll find the Transactions. xlsx file in the Sample Files section of the companion CD.

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

image from book
Figure 22-23: 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 22-24.

image from book
Figure 22-24: In this table, daily data is grouped by months and then by quarters.



Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net