Pinterest Pixel

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... read more

Download Excel Workbook
Bryan
Posted on

Overview

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 - SOLUTION!

 

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.

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

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 - SOLUTION!

STEP 2: Select the option – Group

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

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 - SOLUTION!

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

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

 

To group data by week,

  • Select Days
  • Type Number of days as 7

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

Your Grouped Dates by Week is ready!

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

 

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 - SOLUTION!

 

 

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:

Download excel workbook Errors-when-Grouping-By-Dates.xlsx

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 - SOLUTION!

However, we notice that we have an error!

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

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 - SOLUTION!

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

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

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 - SOLUTION!

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 - SOLUTION!

The invalid dates are now highlighted:

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

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

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

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 - SOLUTION!

Right-click on the Pivot Table and click Refresh:

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

“Check” the ORDER DATE Field:

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

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

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

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

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

Your Grouped Data looks like this:

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

 

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 - SOLUTION!

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 - SOLUTION!

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 - SOLUTION!

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 - SOLUTION!

Click OK.

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

STEP 3: Fix the error in those cells.

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

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

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

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

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

 

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:

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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

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

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!