Errors when grouping by dates

With Pivot Tables, we usually group dates to aggregate our sales numbers. And sometimes we get these dreaded errors when grouping by dates :

The most common reason for facing this problem is that some of the cells contain invalid data. The date column might contain blank cells, text values, or an error.

Unfortunately, this message does not help you with the reason behind the error. So, you need to find ways to spot those errors and fix them one by one!

Let us quickly get to the bottom of those errors when grouping by dates.

 

Exercise Workbook:

---

This is our current Pivot Table setup:

 

Method 1: Use Go To Special

STEP 1: Right-click on any date in the Pivot Table and select Group

We get the “Cannot group that selection” error message. There is something wrong with our dates in the data.

STEP 2: Let us look for errors! Select the ORDER DATE column and press CTRL + G then click Special

STEP 3: Select Constants, Text, and Errors.

This will get the dates that have errors. Click OK.

STEP 4: The errors are now selected although we cannot see them. Click highlight and select any color.

STEP 5: Click on the arrow beside the column and select Filter by Color

You will now see the dates with errors. Let’s make a quick fix with these 4 dates.

Now the dates are fixed.

STEP 6: Right-click anywhere on your Pivot Table and select Refresh

STEP 7: Right-click on any date in the Pivot Table and select Group

It is working now!

 

Method 2: Use Filters

A quick and easy way to find invalid data in the date column is to use the filter buttons. The filter dropdown will group all valid date values and all the dates containing errors will be left at the bottom of the list (ungrouped).

This is because Excel will not recognize those error values as dates and will store them as text.

In the example below, let us use the filters to find out the cells containing invalid data!

STEP 1: Click anywhere on the table.

STEP 2: Go to Data > Filter.

STEP 3: Click the filter button for the ORDER DATE column.

As you can see all the correct dates have been grouped and the incorrect ones have been listed below.

STEP 4: Uncheck the date groups and click OK.

Now the column will display only the incorrect dates.

Let us quickly fix them and see how the grouping feature starts working again in no time!

 

Conclusion

In this article, you have learned how to fix errors when grouping by dates in Excel Pivot Table. Did you know there are many creative ways of doing grouping in Excel Pivot Tables? Learn all about it here!

 

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