In our Group By Date post, we grouped our sales numbers by weeks.

However there are times when we try to group by Dates and we get an error message!  That is frustrating!

Instead of checking the dates one by one to find out where the error occurred, I will show you a cool way to get to those problematic dates & fix them in one go!

We already have the pivot table ready for grouping by Dates:

In the example below I show you how to get the Errors when Grouping By Dates:


 STEP 1: Right click on any row in your Pivot Table and select Group so we can select our Group type that we want:

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 Grouping Options are now displayed!  Your data is now clean!



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


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Insert Blank Rows In a Pivot Table Pivot Table reports are shown in a Compact Layout format as a default and if you have two or more Items in the Row Labels (e.g.Month & Customer), then the Pivot Table report can look very clunky... There is a cool little trick that most Excel users do not know about th...
Top 3 Excel Pivot Table Issues Resolved I have shown my Free Pivot Table webinar to over 40,000 people over the last couple of years and I continually get the same questions from my webinar attendees regarding the little issues they have when using a Pivot Table. I want to put these issues to bed so you can go o...
Classic Pivot Table Layout View For all of you old schoolers out there who used to use the drag and drop Pivot Table option in Excel 2003 or prior, well you still have that option in Excel 2010 and onwards. To activate this option you need to Right Click in your Pivot Table and choose Pivot Table Options. ...
How to Group Worksheets in Excel Ever had a time when you needed to modify data across multiple worksheets?  It is very easy to do this using the Group Worksheets feature in Excel! Let us say we have this same mistake on multiple worksheets -  see the Dvv typo in the screenshot below.  We want to change this ...