Excel Pivot Table Date Grouping is a very powerful feature in Excel that allows you to quickly group dates into years, quarters, months, weeks, days, hours, minutes and/or seconds.

However, there are times when Excel Pivot Table dates cannot group that selection and we get an error message: Cannot group that selection.

Cannot group that selection in an Excel Pivot Table

 

Instead of checking the dates one by one to find out where the error occurred, I will show you a quick way to fix the Cannot group that selection error message!

In this article, you will go through a detailed guide on:

Let’s discuss each of these points one after the other!

 

How to Group Dates in Excel

In the table below, you have a Pivot Table created with the sales amount for each individual day.

To learn how to create a Pivot Table in Excel  – Click Here.

Cannot group that selection in an Excel Pivot Table

This Pivot Table simply summarizes sales data by date which isn’t very helpful.

You might want to see the total sales achieved per month, week, or year. Using formulas or VBA to get the work done will be too complicated.

Want an Easy Fix?? Try Excel Pivot Table Date Grouping!!

Follow the steps below to understand how to Group Dates in Excel Pivot Table:

STEP 1: Right-Click on the Date field in the Pivot Table.

Cannot group that selection in an Excel Pivot Table

STEP 2: Select the option – Group

Cannot group that selection in an Excel Pivot Table

STEP 3: In the dialog box, select one or more options as per your requirement.

To Group Dates by Year and Month.

  • Select Month & YearClick OK.

Cannot group that selection in an Excel Pivot Table

STEP 4: Your Pivot Table with Grouped Dates by Year & Month is ready!

Cannot group that selection in an Excel Pivot Table

 

To group data by week,

  • Select Days
  • Type Number of days as 7

Cannot group that selection in an Excel Pivot Table

Your Grouped Dates by Week is ready!

Cannot group that selection in an Excel Pivot Table

 

Another way to access the Grouping dialog box, Go to Pivot Table Tools > Analyze > Group Selection

Cannot group that selection in an Excel Pivot Table

 

 

Reasons for “Cannot group that selection” Error in Excel Pivot Table Date Grouping

The most common reason for facing this issue is that the date column contains either

  • Is Blank
  • Contains Text
  • Contains an Error.

If even one of the cells contains invalid data, the grouping feature will not be enabled.

Pivot Table won’t allow you to group dates and you will get a cannot group that selection error.

So, the ideal step would be to look for those cells and fix them!

Below are the 2 Quick and Easy methods to find the cells containing invalid data and disappear the errors!

Quick ways to Fix the “Cannot group that selection” Error

In the example below I show you how to get the Errors when Grouping By Dates:

Cannot group that selection in an Excel Pivot Table - SOLUTION! | MyExcelOnline

Follow the step-by-step tutorial on how to fix error in Excel Pivot Table Date Grouping and make sure to download the Excel Workbook and follow along:

DOWNLOAD EXCEL WORKBOOK

METHOD 1:

STEP 1: Right-click on any row in your Pivot Table and select Group so we can select our Group type that we want:

Cannot group that selection in an Excel Pivot Table

However, we notice that we have an error!

Cannot group that selection in an Excel Pivot Table

STEP 2: To check where our error occurred, go to the data table and highlight the column that contains our dates.

Cannot group that selection in an Excel Pivot Table

STEP 3: Go to Home > Find & Select > Go To Special:

v

Make sure the Constants, Text, and Errors are selected.  This will select all our invalid dates (Errors) and text data (Text).

Cannot group that selection in an Excel Pivot Table

STEP 4: Excel has now selected the incorrect dates.  To make each incorrect cell easier to view go to Home > Fill Color

Cannot group that selection in an Excel Pivot Table

The invalid dates are now highlighted:

Cannot group that selection in an Excel Pivot Table

STEP 5: Manually replace the incorrect dates with the correct dates:

Cannot group that selection in an Excel Pivot Table

STEP 6: We need to Refresh our pivot table to load our new correct dates but first we need to “uncheck” the ORDER DATE field.

Cannot group that selection in an Excel Pivot Table

Right-click on the Pivot Table and click Refresh:

Cannot group that selection in an Excel Pivot Table

“Check” the ORDER DATE Field:

Cannot group that selection in an Excel Pivot Table

STEP 7: Right-click on the Pivot Table and click Group:

Cannot group that selection in an Excel Pivot Table

The Excel Pivot Table Date Grouping is now displayed!  Your data is now clean!

excel pivot table date grouping

Your Grouped Data looks like this:

Cannot group that selection in an Excel Pivot Table

 

METHOD 2:

If you look at the Data Table, one of the cells contains a Date with incorrect format (Excel stores it as text) and a Text Value.

Cannot group that selection in an Excel Pivot Table

When you try to Group this Data, you will see that Excel Pivot Table not grouping dates and will display this Cannot group that selection error.

Cannot group that selection in an Excel Pivot Table

Now, to fix this you can simply use the filter button to find the cells containing incorrect format or text.

STEP 1: Go to Data > Filter icon

Cannot group that selection in an Excel Pivot Table

STEP 2: In the Filter dropdown, you will be able to easily spot these cells.

Select only those values.

Cannot group that selection in an Excel Pivot Table

Click OK.

Cannot group that selection in an Excel Pivot Table

STEP 3: Fix the error in those cells.

Cannot group that selection in an Excel Pivot Table

STEP 4: Go back to the Pivot Table, Select PivotTable Analyze > Refresh.

Cannot group that selection in an Excel Pivot Table

STEP 5: Try grouping the data again. Voila! It’s done now.

Cannot group that selection in an Excel Pivot Table

 

Conclusion

In this article, you have learned how to group dates in an Excel Pivot Table and fix the cannot group that selection error when the Pivot Table group by month is not working.

Did you know there are many creative ways of doing grouping in Excel Pivot Tables? Learn all about it here!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Cannot group that selection in an Excel Pivot Table

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!

Excel Pivot Table

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn