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

Add Columns to Pivot Table Slicer Buttons When you insert an Excel Slicer with your Pivot Table it defaults to one column, showing all your items in a vertical layout. Say that your Pivot Table Slicer is showing months from January to December.  You can change the layout of the buttons to show in 3 separate column...
Show Averages With an Excel Pivot Table A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button. The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Produ...
Show The Percent of Grand Total With Excel Pivot T...   Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF GRAND TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sal...
Drill Down To Audit Using a Pivot Table When you are using a Pivot Table in Excel and want to know what data makes up a certain value, all you have to do is double click on that cell. This will open up a brand new Sheet with all the rows of data that make up that value. NB. This is an extraction of your data sour...