Pivot Table: “Cannot Group That Selection” error

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
I have a data entry sheet with a column titled “Start Date”. This column contains only dates.

I have a Pivot Table based on the data entry sheet. In the Pivot Table “Start Date” is a Column Field. Start Date is also used as the data field (Count of Start Date).

I clicked on the “Start Date” In the Column Filed area and selected Grouping by Month & Year.

While I was developing the spreadsheet, I used some arbitrary dates in the Start Date column, and the grouping worked fine. Then I brought in a column of dates from the ‘real’ data entry sheet, and now the grouping is not functional (“Cannot Group That Selection” error).

Can you tell me some possible causes of this error?

Thanks.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Make sure that

a) there are no empty spaces in the data
b) all the dates are real dates (You can check that by changing the format in the database, and if there are some dates that don't change, they need to be "converted" to numbers again)
c) try creating the pivot table again.
 
Upvote 0
I had the same problem with the "Cannot Group That Selection" error. I did not have any empty spaces and all of my dates were formatted properly.

However, the date field I was trying to group was located in the Page Field section (top left corner filters). Once I moved the field into the main body of the pivot table, I was able to group the dates. This worked for me!
 
Upvote 0
This cannot group problem just stops us in our tracks. It blocks us from benefitting from the features of pivot tables. Are there quick ways to implement the solutions and data-checking such as checking for blank cells in a 13,000 row database?
 
Upvote 0
If date formatting or blank cells are not the reason you cannot group on a date column, save the excel file as a text file and reopen. After reformatting the date column you should be able to group on that column.
 
Upvote 0
You can try first creating a pivot table excluding the empty range (only to the extend of range that data is available) and now you should be able to group the date into month or years. Once this is done, you can go back to options, change data source and now you can extend your data range over the empty cells.

Though its im too late ,,,,,,Hope this would work ....... :)

Regards
 
Upvote 0
Make sure that

a) there are no empty spaces in the data
b) all the dates are real dates (You can check that by changing the format in the database, and if there are some dates that don't change, they need to be "converted" to numbers again)
c) try creating the pivot table again.

This is the reason I come to this site. For the past 3 months a pivot table that generates a report has been screwing up in an automated spreadsheet. Backup copies of the file worked, but every time I went to the most recent, or copied the data from the most recent to the backup, the table messed up.
I went to the data and found where someone had messed up the format of one date, fixed that, and the table now works, my reports now come out properly.
Thanks

Paul
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top