Excel Pivot Table Date Grouping is a very powerful feature in Excel that allows you to quickly group dates into years, quarters, months, weeks, days, hours, minutes and/or seconds.
It helps Excel Users save time and provide better analysis!
However, there are times when Excel Pivot Table dates not grouping 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!
In this article, you will go through a detailed guide on:
- How to Group Dates in Excel
- Reasons for Error in Excel Pivot Table Date Grouping
- Quick ways to Fix the Error
Let’s discuss each of these points one after the other!
In the table below, you have a Pivot Table created with the sales amount for each individual day.
To learn how to create a Pivot Table in Excel – Click Here.
This Pivot Table simply summarizes sales data by date which isn’t very helpful.
You might want to see the total sales achieved per month, week, or year. Using formulas or VBA to get the work done will be too complicated.
Want an Easy Fix?? Try Excel Pivot Table Date Grouping!!
Follow the steps below to understand how to Group Dates in Excel Pivot Table:
STEP 1: Right-Click on the Date field in the Pivot Table.
STEP 2: Select the option – Group
STEP 3: In the dialog box, select one or more options as per your requirement.
To Group Dates by Year and Month.
- Select Month & Year. Click OK.
STEP 4: Your Pivot Table with Grouped Dates by Year & Month is ready!
To group data by week,
- Select Days
- Type Number of days as 7
Your Grouped Dates by Week is ready!
Another way to access the Grouping dialog box, Go to Pivot Table Tools > Analyze > Group Selection
The most common reason for facing this issue is that the date column contains either
- Is Blank
- Contains Text
- Contains an Error.
If even one of the cells contains invalid data, the grouping feature will not be enabled.
Pivot Table won’t allow you to group dates and you will get an error.
So, the ideal step would be to look for those cells and fix them!
Below are the 2 Quick and Easy methods to find the cells containing invalid data and disappear the errors!
In the example below I show you how to get the Errors when Grouping By Dates:
Follow the step-by-step tutorial on how to fix error in Excel Pivot Table Date Grouping and make sure to download the Excel Workbook and follow along:
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 Excel Pivot Table Date Grouping is now displayed! Your data is now clean!
Your Grouped Data looks like this:
If you look at the Data Table, one of the cells contains a Date with incorrect format (Excel stores it as text) and a Text Value.
When you try to Group this Data, you will see that Excel Pivot Table not grouping dates and will display this error.
Now, to fix this you can simply use the filter button to find the cells containing incorrect format or text.
STEP 1: Go to Data > Filter icon
STEP 2: In the Filter dropdown, you will be able to easily spot these cells.
Select only those values.
STEP 3: Fix the error in those cells.
STEP 4: Go back to the Pivot Table, Select PivotTable Analyze > Refresh.
STEP 5: Try grouping the data again. Voila! It’s done now.
In this article, you have learned how to group dates in Excel Pivot Table and fix the error when excel pivot table group by month not working.
Did you know there are many creative ways of doing grouping in Excel Pivot Tables? Learn all about it here!
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: