Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
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 '12 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 '12 at 16:06

You must log in to answer this question.

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