Excel Pivot Table Report – Group Items, Group Date and Time Values
As applicable to Excel 2007
Excel Pivot Tables Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Group Items in a Pivot Table report
You can create a group containing the selected items, in the Rows & Columns Areas of a Pivot Table report. Grouping data bunches rows or columns the way you want and which might not be possible with the Pivot Table tools like sorting or filtering.
Group Selected Items:
Refer Image 1 where on the left is the original Pivot Table showing citywise sales. If you want to group cities whose sales are over $11,000 and other cities where sales are less than or equal to $11,000 – this grouping shows in the Table on the right.
In this example we selected the 3 cities of ‘Europe’ and 2 cities of ‘NorthAmerica’ having Sales > 11,000, clicked on ‘Group Selection’ in the group named ‘Group’ (in the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon) which created a new group whose default name was ‘Group1’. We repeated the process by selecting 1 city of ‘Europe’ and 2 cities of ‘NorthAmerica’ having Sales <= 11,000 which created a new group whose default name was ‘Group2’. We selected the cell containing ‘Group1’, pressed F2, and changed the group name to ‘Sales>11,000’ and similarly changed the name of ‘Group2’ to ‘Sales<=11,000’. On grouping the selected items, a separate field is created with the name ‘City2’ which we renamed to ‘Group by Sales’. You can similarly group cities with the top 3 Sales, or select unrelated cities and group. Note that a minimum of 2 items need to be selected for grouping.
Manipulate the new Grouped Field:
You can manipulate the newly created grouped field similar to other fields by renaming it, move to another area in the Pivot Table report, change its order within an area, sort and filter, but a calculated item cannot be added to this grouped field.
Ungroup Items:
To ungroup a range of cells that were previously grouped, click on ‘Ungroup’ in the group named ‘Group’ (in the ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon). Selecting the field name viz. ‘Group by Sales’ and then clicking on ‘Ungroup’ will ungroup all groups and the new field will get removed. Clicking on a specific group viz. ‘Sales>11,000’ and then clicking on ‘Ungroup’ will ungroup only the items of that group. You can also use the ‘Ungroup’ command by selecting the group name or the new field name and then right-click.
Group Numeric Items:
Refer Image 2 which shows the original Pivot Table at the top and on its right is the ‘Grouping’ dialog box (which opens if you select a field with numeric items and then click ‘Group Field’ in the group named ‘Group’) where you enter a number in the ‘Starting at’ box and in the ‘Ending at’ box which will be the starting item and ending item you want to group in the Pivot Table. In the ‘By’ box enter the interval to be included in each group. In this example we have covered all items (ie. months) from 1 to 12 (by selecting ‘Auto’ checkboxes for both ‘Starting at’ and ‘Ending at’) and the interval is each quarter ie. months 1-3 are bunched together, months 4-6 are bunched, and so on. The Pivot Table with grouped numeric items appears below the original Pivot Table in Image 2.
Ungroup: Select the numeric field and then click on ‘Ungroup’, this will ungroup all groups.
Group Date & Time Values:
Refer Image 3 which shows the original Pivot Table at the top and on its right is the ‘Grouping’ dialog box (which opens if you select a field with Date or Time values and then click ‘Group Field’ in the group named ‘Group’) where you enter a Date/Time in the ‘Starting at’ box and in the ‘Ending at’ box which will be the starting date/time and ending date/time you want to group in the Pivot Table. In the ‘By’ box select the interval to be included in each group (multiple selections are allowed here). In this example we have entered 5/31/2012 as the starting date and 12/31/2012 as the ending date. In the ‘By’ box we have selected both ‘Months’ and ‘Quarters’. The Pivot Table with grouped date values appears below the original Pivot Table in Image 3. Note that a new field of ‘Quarters’ gets created because we have made multiple selections in the ‘By’ box.
Ungroup: Select a field with Date or Time values and then click on ‘Ungroup’, this will ungroup all groups and the new field (if created while grouping) will also get removed.