Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!

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:

Pivot Table Terminology

Creating a Pivot Table Manually

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 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 name Western Zone and Eastern Zone.

Grouping pivot table items

A simple grouping example. States are grouped into two zones.

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.

How to group items in pivot tables

Same pivot tables shown in four different 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 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.

How to group items in a Pivot Table report

Sales day by date. This table has 730 rows covering data between 1st January 2012 and 31rd 2013

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.

How to group items in a Pivot Table report

The pivot table is almost the same as the input data.

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.

How to group items in a Pivot Table report

Grouping dialog box. You can enter Starting at and Ending at data in this dialog box.

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.

How to group items in a Pivot Table report

The pivot table, after grouping by month and year.

Note: You can select only Months in the By list box in the Grouping dialog box, months in different year will be combined together. For example, the March item would display the sum of sales for year 2012 and 2013.

The following figure shows one more pivot table grouped by quarter and by year.

How to group items in a Pivot Table report

Items grouped by quarter and 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.

How to group items in a Pivot Table report

We shall use this table to show you how to group items by hours.

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.

How to group items in a Pivot Table report

Finally, the pivot table is grouped by hours.

If your computer use 24 h time format you will find this type of pivot table:

How to group items in a Pivot Table report

Pivot table grouped by hours in 24-H time format.

Happy Excelling 🙂

Download Working Files

Employee-list3.xlsx

Time-based-grouping.xlsx


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply