Cannot group that selection in an Excel Pivot Table – SOLUTION!

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.

 

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!

 

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.

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.

STEP 2: Select the option – Group

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

To Group Dates by Year and Month.

• Select Month & Year. Click OK.

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

 

To group data by week,

• Select Days
• Type Number of days as 7

Your Grouped Dates by Week is ready!

 

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

 

 

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:

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:

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:

However, we notice that we have an error!

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

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

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

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

The invalid dates are now highlighted:

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

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.

Right-click on the Pivot Table and click Refresh:

“Check” the ORDER DATE Field:

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

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

Your Grouped Data looks like this:

 

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.

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.

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

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

Select only those values.

Click OK.

STEP 3: Fix the error in those cells.

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

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

 

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!

Further Learning:

• Format Error Values in a Pivot Table
• Prepare Data for Excel Pivot Tables
• Errors when grouping by dates

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

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