3

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 2

1

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.

3
  • 1
    You may need to remove and re-add the field to the Pivot Table before it will group, I find it seems to cache the field type and won't group dates if one was blank (so it treats a whole column as text, grrr!) and is then later filled in.
    – AdamV
    Oct 9, 2012 at 11:24
  • +1 on Adam's comment. I've had the same experience in the past, come to think of it.
    – nutsch
    Oct 9, 2012 at 16:06
  • You can also Refresh the data without readding the field before it will group. PivotTable Tools/Analyze -> ` Refresh` dropdown above the Data section. Aug 9, 2023 at 9:33
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .