Pinterest Pixel

Errors when grouping by dates

With Pivot Tables, we usually group dates to aggregate our sales numbers. And sometimes we get these... read more

Download Excel Workbook
Bryan
Posted on

Overview

Filter by Values - Between

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 workbookErrors-when-grouping-by-dates.xlsx


This is our current Pivot Table setup:

Errors when grouping by dates

 

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

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

See also  Filter by Values - Top or Bottom Sum

Errors when grouping by dates

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

Errors when grouping by dates

STEP 3: Select Constants, Text, and Errors.

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

Errors when grouping by dates

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

Errors when grouping by dates

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

Errors when grouping by dates

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

Errors when grouping by dates

Now the dates are fixed.

Errors when grouping by dates

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

Errors when grouping by dates

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

Errors when grouping by dates

It is working now!

Errors when grouping by dates

 

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

See also  Highlight Cell Rules based on text labels

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

STEP 2: Go to Data > Filter.

Errors when grouping by dates

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

Errors when grouping by dates

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

Errors when grouping by dates

STEP 4: Uncheck the date groups and click OK.

Errors when grouping by dates

Now the column will display only the incorrect dates.

Errors when grouping by dates

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

Errors when grouping by dates

 

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!

See also  Group Periods In Microsoft Excel Pivot Tables

 

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

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

Errors when grouping by dates | 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!