Errors when grouping by dates | MyExcelOnline

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

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:

DOWNLOAD EXCEL WORKBOOK


This is our current Pivot Table setup:

Errors when grouping by dates | MyExcelOnline

 

Method 1: Use Go To Special

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

STEP 3: Select Constants, Text, and Errors.

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

Now the dates are fixed.

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

It is working now!

Errors when grouping by dates | MyExcelOnline

 

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.

Errors when grouping by dates | MyExcelOnline

STEP 2: Go to Data > Filter.

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

STEP 4: Uncheck the date groups and click OK.

Errors when grouping by dates | MyExcelOnline

Now the column will display only the incorrect dates.

Errors when grouping by dates | MyExcelOnline

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

Errors when grouping by dates | MyExcelOnline

 

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!

 

HELPFUL RESOURCE:

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

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn