Excel Pivot Table Not Grouping Dates by Month (4 Solutions)

Dataset Overview

The image below displays our dataset. We’ll highlight common errors that might occur during the solution process and guide you on how to address them.

Solution 1 – Using the Proper Format for Grouping by Month

If any dates in your dataset are not in the correct format, you won’t be able to group the pivot table by month. Follow these steps to resolve the issue:
- Select the range B4:E12 for the Pivot Table.
- Go to Insert and select Pivot Table.
- Click OK.

- A new sheet will display the Pivot Table features.
- Drag the Dates and Sales ranges to the Rows and Values fields, respectively.

- You’ll now see information about Sales in the Pivot Table.

- Observe that there are two dates displayed in number format. Right-click on any of the dates and select Group from the context menu. You’ll encounter an error.

To resolve this error:
- Return to your sheet and convert the dates to the proper format by selecting Short Date from the Number Group.

- Convert the dataset to a Table by selecting the entire dataset and going to Insert and selecting Table.
- Ensure you select My table has headers.

- This operation will convert the dataset to a Table. Select the table and choose Pivot Table again from the Insert tab.
- Drag the Dates and Sales range to the Rows and Values fields.
- An additional field called Month will automatically appear in the Rows section.

- Your Pivot Table will now display Sales information by Month.

- To view monthly sales information by specific dates, click on the plus icon next to the months.

By following these steps, you can successfully address the issue of Pivot Table not grouping dates by month.

Solution 2 – Converting Text-Formatted Dates to Numeric

Another common reason for encountering the “Cannot group that selection” error is when dates are stored in Text Format. Let’s walk through the steps to address this issue:

Take a look at the dates in your dataset. If they appear on the left side of the cell, it indicates that they are in Text Format.

Unfortunately, you won’t be able to group these text-formatted dates by month in the Pivot Table, and attempting to do so will result in the error message.

To resolve this, convert the dates to a proper date format. In some cases, you might notice an apostrophe or comma (‘) before each date. You can remove these using the Find & Replace feature. If that doesn’t work, manually remove them.
Once the dates are in the correct format, proceed to the next steps.

Convert your data to a Table. Select the entire dataset and go to Insert and select Table.
Make sure to select My table has headers.
Follow the procedure from Method 1 to insert the dates and sales into their respective fields in the Pivot Table.
You’ll notice that your Pivot Table now displays sales information by month.

Click on the plus icon next to the months to view monthly sales information by specific dates.
If desired, you can also add the profit range to the Values field.

Solution 3 – Using the 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.

Select the date range that is currently in text format.
Go to the Data tab.
Click on Text to Column.

Follow the instructions in Method 2 to see the problem that occurs when using dates in text format in a Pivot Table.
After applying Text to Column, click on Finish.

You’ll notice that the dates are now converted to date format.

Convert this dataset into an Excel table.

Use the table you created in the previous step to build your Pivot Table.
Follow the procedure from Method 1 to insert dates and sales into the respective fields of the Pivot Table.

Your Pivot Table will now display sales information by month.
To see monthly sales information by specific dates, click on the plus icon next to the months.
You can also add the Profit range to the Values field.

By following these steps, you’ll be able to resolve the issue of the Pivot Table not grouping dates by month.

Solution 4 – Using Dates Instead of Months

If your dataset contains only month names, you won’t be able to directly group dates by month in a Pivot Table. Attempting to do so will result in the “Cannot group that selection” error. Let’s walk through the process below:

Suppose your dataset looks like the image provided.

You’ll need to replace the month names with actual dates.
To achieve this, create two columns: one for the month names and another for their corresponding dates.

In cell B5, enter the following VLOOKUP formula:

=VLOOKUP(G5,$G$4:$H$12,2,FALSE)

The VLOOKUP function will return the date from cell H5 into cell B5.
Press ENTER, and you’ll see the date stored in cell H5 appear in B5.

Use the Fill Handle to Autofill the formula down to the lower cells.

Select the range B4:E12 and convert it into an Excel table.
Follow the instructions in Method 1 to group the data by month.
Note that using the VLOOKUP function will only provide the first date of each month, so you won’t get sales information date by date within each month.