The ability to combine items in groups is one of the most useful features of a pivot table. You can group items that are placed in the ROWS or COLUMNS section in the PivotTable Fields task pane. You can use two ways to group items: manually and automatically.
To master this article, you have to read these articles:
Table of Contents
Grouping Items Manually in a Pivot Table
To group items, first of all, you have to create a pivot table from your source data. After creating the pivot table, select the items you want to group and then choose PivotTable Tools ➪ Analyze ➪ Group ➪ Group Selection. Or select the items in the pivot table, right-click, and choose Group from the shortcut menu.
Manual grouping in a pivot table: Explained with an example
The following figure shows a pivot table, with two groups (Western Zone and Eastern Zone), created from the items in the Row Labels (Location field). To create the first group, press the CTRL key on your keyboard, hold the key, and select Arizona, California, and Washington. Then I right-clicked and chose Group from the shortcut menu. Then I selected the other three states and created a second group in the same way. I have replaced the group names with two meaningful names Western Zone and Eastern Zone.
You can create any number of groups and even you can create groups of groups.
Excel provides a good number of options for displaying your pivot table. When you create groups of items in a pivot table, you may want to use these styling options. You will find these commands on the PivotTable Tools ➪ Design tab of the Ribbon. You can try several designs and choose one which best suits your pivot table in a visualization perspective. A suitable style can greatly enhance readability.
The following figure shows some pivot tables manipulated using various options for displaying subtotals, grand totals, and styles.
Automatic Grouping Examples
You can use the Grouping dialog box to group items if the items are numeric or dates. To group items in this way: select any single item and then choose PivotTable Tools ➪ Analyze ➪ Group ➪ Group Field. Or you can right-click a single item and choose Group from the shortcut menu. In either case, the Grouping dialog box will appear. Using this dialog box, you can group the items.
Read More: Multiple Groups from the Same Data Source
Automatic Grouping in a Pivot table, Explained with Example
When a data source field contains numbers, dates, or times, Excel can create groups automatically.
We shall demonstrate automatic groupings using two examples.
1) Automatic Grouping by Date
The following figure shows a portion of a simple table. The table has two fields: Date and Sales. This table has a total of 730 rows and these rows cover sales data between 1st January 2012 and 31st December 2013. Our goal is to summarize the information by month using a pivot table.
The following figure shows part of a pivot table created from the above data. We have placed the Date field is in the Rows area, and the Sales field is in the Values area. This pivot table looks exactly like the data source as the dates are unique.
We want to group the items by month and year. To group the items by month and year, select any date in the pivot table and choose PivotTable Tools ➪ Analyze ➪ Group ➪ Group Field (or right-click any date and choose Group from the shortcut menu). The Grouping dialog box will appear shown in the following figure. Excel supplies values automatically for the Starting At and Ending At fields. Starting At and Ending At fields cover the data range and you can change if you want to shorten your data range.
Select Months and Years from the By list box. Click OK. The Date items in the pivot table are grouped by years and by months, as shown in the following figure.
Note: You can select only Months in the By list box in the Grouping dialog box, months in a different year will be combined together. For example, the March item would display the sum of sales for the years 2012 and 2013.
The following figure shows one more pivot table grouped by quarter and by year.
2) Grouping Items by Time
Say we are experimenting with something with an instrument. The instrument is reading data at one-minute intervals throughout the whole day. The following figure shows a part of the table.
We are going to create a pivot table that will use this table to group items by hours. Here are the steps that I have used to create the pivot table:
- We have made three instances of the Reading field in the Values area in the PivotTable Fields task pane. Three instances display respectively Average, Minimum, and Maximum summary methods. If you don’t know how to change the summary method for a column, use this method: right-click any cell in the column, a shortcut menu will appear, choose the Summarize Values By option and then select your appropriate option from Sum, Count, Average, Max, Min, Product, and other choices.
- I have placed the Time field is in the Rows area, and I used the Grouping dialog box to group the items by Hours.
- I have deselected the Field Headers from the pivot table using this command, changed the headings of columns, and used a style to make it more readable.
The following figure displays the pivot table grouped by hours.
If your computer use 24 h time format you will find this type of pivot table:
Happy Excelling 🙂