The article will show you how to fix the issue of Pivot Table not grouping dates by month. When you work with a Pivot Table, it’s convenient for you to group your sales information by dates or months. Because this will give you an overall summary of sales or profit in a period. But sometimes, you may face some error which is known as ‘Cannot group that selection’ while doing Pivot Table analysis with dates and months. This problem may arise due to dates being in text format or some other errors. I’ll show you how to get rid of this issue in this article.
Excel Pivot Table Not Grouping Dates by Month
The following picture shows our dataset. I’ll put some errors that may occur frequently in the solution processes and also show you how to get rid of them.
1. Using the Proper Format of Dates to Group by Month
If any of the dates in your dataset are not in a proper format, you cannot group the pivot table by month. Let’s follow the instructions below.
Steps:
Suppose, your date is not in a date format. You may have a large dataset and this nuisance can happen eventually.
- Select the range B4:E12 for the Pivot Table.
- After that, go to Insert >> Pivot Table.
- Next, click OK.
- Thereafter, you will see the Pivot Table features in a new sheet.
- Drag the Dates and Sales ranges to Rows and Values fields respectively.
- After that, you will see the information about Sales in the Pivot Table.
- Notice that there are two dates in number format. Open the context menu by right-clicking on any of the dates and select Group.
After that, you will see the following error.
- To eliminate the error, you need to go back to your sheet and convert the dates to proper format by selecting Short Date from the Number Group.
- After that, convert the dataset to a Table. For that reason, select your entire dataset and then go to Insert >> Table.
- Make sure you select ‘My table has headers’.
- Thereafter, this operation will convert the dataset to a Table. Now select the table and choose Pivot Table again from the Insert Tab.
- Drag the Dates and Sales range again to the Rows and Values
- You will see another field Month will appear automatically in the Rows
- You will see your Pivot Table carries the Sales information by Month.
- You can also see the monthly Sales information by dates by clicking on the plus icon beside the months.
Thus you can solve the issue of Pivot Table not grouping dates by month.
Read More: [Fix] Cannot Group Dates in Pivot Table
2. Converting Text Format of Dates to Number
Another reason you may get the ‘Cannot group that selection’ error is because of dates being in Text format. Let’s go through the procedure below to solve the matter.
Steps:
First, observe the dates in the dataset. They are on the left side of the cell meaning they are in Text format.
Of course, you will not be able to use these dates to group your data by month in the Pivot Table. You will get the ‘Cannot group that selection’ error while grouping them in that Pivot Table.
- In order to get rid of this error, convert the dates to date format. In my case, there was an apostrophe comma (‘) before each date. You can remove them using the Find & Replace feature. If that does not work, remove them manually. I had to do that manually.
- After that, convert this data to a Table and use this table to create a Pivot Table.
- Also, follow the procedure of Section 1 to insert dates and sales into the respective fields of the Pivot Table.
- Later, you will see your Pivot Table carries the Sales information by Month.
- You can also see the monthly sales information by dates by clicking on the plus icon beside the months. You can add the Profit range in the Values field too.
If you want to understand what the Rows and Values fields are, please go to this link in Section 1.
Thus you can solve the issue of Pivot Table not grouping dates by month.
3. Applying Text to Column Wizard
You can also convert the text format of dates by the Text to Column feature in order to eradicate the ‘Cannot group that selection’ error of the Pivot Table. Let’s go through the discussion below.
Steps:
- First, select the date range that is in text format.
- After that, go to Data >> Text to Column.
To see the problem that occurs when you use the dates in text format in a Pivot Table, please go through Section 2.
- Next, you will see the Text to Column Just click on Finish.
- After that, you will see the dates converted to date format.
- Thereafter, convert this dataset into a table.
- Next, use this table to create the Pivot Table. If you need to see how to create a Pivot Table then go to Section 1.
- Also, follow the procedure of Section 1 to insert dates and sales into the respective fields of the Pivot Table.
- Later, you will see your Pivot Table carries the sales information by month.
- You can also see the monthly sales information by dates by clicking on the plus icon beside the months. You can add the Profit range in the Values field too.
If you want to understand what the Rows and Values fields are, please go to this link in Section 1.
Thus you can solve the issue of Pivot Table not grouping dates by month.
4. Using Dates Instead of Months
If your data contains only months, you cannot use that dataset to group dates by month in a Pivot Table. Cause you will get the ‘Cannot group that selection’ error. To know when this error occurs, follow this link in Section 1. Let’s go through the process below.
Steps:
- Suppose, your dataset looks like the following image.
- You need to replace these months with dates. In order to do that, create two columns with these months and their corresponding dates.
- After that, type the following formula in cell B5.
=VLOOKUP(G5,$G$4:$H$12,2,FALSE)
Here, we used the VLOOKUP Function in the formula which will return the date of cell H5 in B5.
- Hit the ENTER button and you will see the date that is stored in cell H5.
- After that, use the Fill Handle to AutoFill the lower cells.
- Now again, select the range B4:E12 and convert it to a table.
- After that, follow this link in Section 1 to group the data by month. However, you won’t get the sales information date by date in each month as the VLOOKUP function only takes the first date of each month.
Thus you can solve the issue of Pivot Table not grouping dates by month.
Download Practice Workbook
Conclusion
In the end, we can surmise the fact that you will get the basic concept of solving Pivot Table not grouping dates by month. If you have any better methods questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- How to Group by Week in Excel Pivot Table
- How to Group by Year in Excel Pivot Table
- How to Group by Week and Month in Excel Pivot Table
- How to Group by Month and Year in Excel Pivot Table
<< Go Back to Group Dates in Pivot Table | Group Pivot Table | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
