Beginners Guide: How To Group Dates In Pivot Table

Mar 11, 2023

Would you like to learn how to group dates in pivot tables?

Dates can be hard to group because they have special meanings and structures. In this post, we'll show you how to create groups from your dates so you can perform a more insightful analysis.

Let's dive right in and discuss the following:

Automatic Date Grouping Feature
Disable Automatic Grouping
Ungroup Data Feature
Group Dates
Final Thoughts on How to Group Dates in Pivot Table
Frequently Asked Questions on How to Group Dates in Pivot Table

Automatic Date Grouping Feature

Excel 2016 and all the later versions make grouping dates in a pivot table simple by adding a date field. The feature arranges your data into categories automatically. However, you can reverse this action within Excel 2016. On more current versions, you can disable it entirely.

Disable Automatic Grouping

If you're using Excel 2019 or Office 365, you can disable automatic grouping by updating the settings. Doing so will provide a smoother spreadsheet experience and ensure accuracy in data entry.

To disable the automatic date grouping feature:

Go to the File Tab.
In the File tab, go to "More," then click "Options."
In the Excel Options dialog box, click "Data," select "Disable Automatic Grouping," then click "OK."

Ungroup Data Feature.

You can manually ungroup the dates in Excel 2016 and later versions with one of these methods:

Keyboard Shortcut.

Include the Date field in your pivot table.

To instantly undo the automatic date grouping, press Ctrl + Z.

By removing the grouped fields, only the original Date field will remain in your pivot table.

Ungroup Function.

Right-click any cells in the date field, and select Ungroup.

Doing so will ungroup the dates.

Group Dates.

In a pivot table, you can categorize data/dates in various ways - from seconds and minutes to hours, days, months, quarters, and even years.

Several options are available to create a date grouping that meets your needs! See the examples below to learn how to formulate the perfect data classification.

How to Group Data by Months/Year.

To group dates by months/years in date fields:

Right-click any dates in the pivot table, and select "Group" in the pop-up menu.

In the Grouping dialog box, select the Years and Months grouping option.

To choose which dates you want to use, type the first date in the "Starting at" box and the last date in the "Ending at" box. Finally, click "OK."

Date Groupings by Week.

To group dates by week in date fields:

Right-click any dates in the pivot table and select "Group" in the Excel pop-up menu.

In the Grouping dialog box, select the Days grouping option.

In the "Number of days" option, choose 7.

The "Starting at" date determines when the week starts. You can change this date if needed. For example, if you wanted the week to begin on December 31, 2012, you should enter that date in the box. Once you have entered the date, click "OK."

Final Thoughts on How to Group Dates in Pivot Table

Our tips should help you group data by date in Pivot Table. Consequently, you can avoid making mistakes because you have the correct order of dates. Try these quick fixes if you still can't group dates in a pivot table.

You can visit our Simple Sheets for more easy-to-follow how-to guides for spreadsheets. Check the links in related articles for further details about Excel/Google Sheets templates!

Frequently Asked Questions on How to Group Dates in Pivot Table:

Why can't I group data of dates in a Pivot Table?

The system will not activate the grouping feature if any cells contain invalid data. Pivot tables won't allow you to group dates, and you will get an error that says, "Cannot group that selection."

Why aren't my dates grouped in Excel?

In Excel, go to File, and click on Options (usually in the lower-left corner of the screen). Go to the Advanced tab in the left pane of the Options window. Scroll to the workbook settings and select "Group dates in the AutoFilter menu."

How do you group dates in a table?

Select any cell in the Date column of the pivot table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialog box, select Months and Years. You can choose more than one option by clicking on it. Click OK.

Excel Cannot Group Dates in Pivot Table: 3 Quick Fixes to Try.

How to Connect Slicers to Multiple Pivot Tables

Pivot Table Google Sheets Walkthrough

Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons

We hate SPAM. We will never sell your information, for any reason.