"Cannot group that selection" error when creating pivot table

I have some data from my friend.

• The first column is time, e.g. 01:02:59
• The second is a number, e.g. 5

I want to create a pivot table and then group the time to be sorted by hours (between 01:00:00 and 01:59:59). I get an error "Cannot group that selection". I'm not sure why this happens.

Also, the time in the original cell is in an unknown format. It's aligned on the left side of the cell and if I double click on the cell, the time will jump to the right side. If I double click on all of them, all will finally be on the right side of cells – it works for grouping and then sorting by hours.

It looks like it is in some strange format – but I can not find in which one or what's wrong.

2 Answers

That's not a strange format, that's text. And until you've converted all cells from text-looking-like-time to actual time (i.e. a number) and refreshed your pivot, you won't be able to group the way you want.

Luckily, you don't have to double-click you way down the whole list. Select the column, run Text to Columns (in the Data menu or AltAE), Delimited, and Finish. The whole column should be converted to numbers. You can now refresh the pivot and should be able to group.

As of Excel 2013, when you create a pivot table, you are given the option, "Add this data to the Data Model" in the "Create PivotTable" dialogue box. Unfortunately, it seems that if you check this option, you will not be able to group any fields and you will always get the "Cannot group that selection" error. So, the workaround is to not choose "Add this data to the Data Model" when creating the pivot table. I don't understand yet very well how data models work in Excel, so I don't know the reason for this conflict.