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

Sort by Largest or Smallest With Excel Pivot Table... There are countless times when I had my Pivot Table all setup, but I was not really happy with how the data was sorted. The cool thing with Excel Pivot Tables, is it allows me to sort virtually anywhere!Let us start off with this Excel Pivot Table showing the Sum of SALES for...
Lock the Excel Pivot Table but not the Slicer Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format.What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interact...
Group By Month With Excel Pivot Tables Before I was a Pivot Table guru, I had to get individual rows of daily sales and group them into a report showing the monthly sales during the year.Grouping these would take a ton of effort using Formulas:Extracting the month and year from each transactional date; Th...
Pivot Table Count Transactions The default Value Field Setting when analyzing data with a Pivot Table is to Sum, but you can also analyze a Pivot Table with a Count.This is useful when you want to see how many sales transactions  took place within a region, a month, a year or per business unit.DOWNLOAD...