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

The image below displays our dataset. I’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 > 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 > Table.
- Ensure you select “My table has headers.”

- 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 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:

Observe the Dates:
- 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.

Convert to Date Format:
- 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.

Create a Table:
- Convert your data to a Table. Select the entire dataset and go to Insert > Table.
- Make sure to select “My table has headers.”

Create the Pivot Table:
- Follow the procedure from Section 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.

Additional Insights:
- 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.

Understanding Rows and Values Fields:
- For a detailed explanation of the Rows and Values fields, refer to the link provided in Section 1.

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:
- Start by selecting the date range that is currently in text format.

Apply Text to Column:
- Go to the Data tab.
- Click on Text to Column.

- Follow the instructions in Section 2 to see the problem that occurs when using dates in text format in a Pivot Table.

Finish the Text to Column Process:
- After applying Text to Column, click on Finish.

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

Convert the Dataset into a Table:
- Convert this dataset into an Excel table.

Create the Pivot Table:
- Use the table you created in the previous step to build your Pivot Table.
- If you need guidance on creating a Pivot Table, refer to Section 1.
Insert Dates and Sales:
- Follow the procedure from Section 1 to insert dates and sales into the respective fields of the Pivot Table.

View Monthly Sales Information:
- 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.

Understanding Rows and Values Fields:
- If you’re unfamiliar with the Rows and Values fields, refer to the link provided in Section 1.

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. To understand when this error occurs, refer to the link in Section 1. Let’s walk through the process below:

Dataset Setup:
- 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.

VLOOKUP Formula:
- In cell B5, enter the following formula:

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

- The VLOOKUP function will return the date from cell H5 into cell B5.

Result and Autofill:
- 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.

Convert to a Table:
- Select the range B4:E12 and convert it into an Excel table.
Group Data by Month:
- Follow the instructions in Section 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.