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

50 Things You Can Do With Excel Pivot Tables Pivot Tables in Excel are one of the most powerful features within Microsoft Excel.   A Pivot Table allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, "pivot"/change the report layout with the ease of dr...
Sorting a Pivot Table There are a few ways that you can Sort a Pivot Table.  You can Sort the Row/Column Labels as well as Values within a Pivot Table.  Below I show you three quick ways...DOWNLOAD WORKBOOKHelpful Resource: 
Pivot Table Report Layouts Pivot Tables have three different layouts that you can choose from: Compact, Outline and Tabular Form.You can choose from each layout by clicking in the Pivot Table and going to PivotTable Tools > Design > Report LayoutsThey each have their advantages and disadvanta...
Repeat All Item Labels In An Excel Pivot Table Whenever you create a Pivot Table the default layout is in Compact Form, which puts all the data in one column.Many people do not like this layout as you cannot copy and paste the data and do further analysis in another worksheet.The best layout to use is either the Outli...