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

Date Grouping is a great feature in Pivot Table that allows you to quickly group dates into years, quarters, months, weeks, days, hours, minutes, or seconds. However, there are times when Excel Pivot Table dates cannot group that selection, and we get an error message. In this article, you will learn how to fix the error of being unable to group data in an Excel Pivot Table.

Key Takeaways

• Date grouping works only with valid date values.
• Blank cells and text values can cause grouping errors.
• Refresh the Pivot Table after fixing the source data.
• Remove invalid dates before grouping.
• Clean data helps Pivot Tables work correctly.

 

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 will need to group the data by date to see the total sales for each month, week, or year.

Follow the steps below 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!

Alternatively, 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!

 

How 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.

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 an incorrect format (Excel stores it as text) and a Text Value.

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

To fix this:

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.

 

Preventive Measures to Avoid Future Grouping Errors

Best Practices

• Make sure that all your data is formatted correctly, with dates, numbers, and text each in their appropriate cells.
• Regularly check for and remove any blank spaces or inconsistencies in your data, as these can cause errors.
• Keep your data tables clean and avoid unnecessary formatting that could confuse Excel’s pivot table algorithms.
• Always validate your data sets before you begin to build pivot tables, double-checking for complete rows and consistent data types.
• Consider using Excel’s Table feature before creating a pivot table, as it helps maintain structured references and can improve the accuracy of your pivot tables.

Checklists Before Creating Pivot Tables

• Data Range: Confirm that the range of your data includes all necessary rows and columns without any extras.
• Consistency: Check that all data formats are consistent, particularly date and number formats.
• Blanks: Look for and eliminate blank cells that might be hiding among your data, as they can cause grouping issues.
• Headers: Ensure each column in your data range has a unique and descriptive header.
• Duplicates: Remove any duplicate records that could skew your data analysis.
• ‘Data Model’ Option: If you don’t need to create relationships between multiple data tables, avoid checking ‘Add this data to the Data Model’ when creating your pivot table.

 

FAQs

1. Why do I get the “Cannot group that selection” error?

It usually happens because of blank cells, text, or invalid dates.

2. Can blank cells stop date grouping?

Yes. Even one blank cell can prevent grouping.

3. Why should I refresh the Pivot Table?

Refreshing updates the Pivot Table with the corrected data.

4. Can text-formatted dates cause grouping errors?

Yes. Excel can only group real date values.

5. How can I prevent grouping errors in the future?

Keep your source data clean and use consistent data formats.