I am attempting to group data by range of value to create a histogram.

When I try to group a set of row labels in the Pivot Table I receive the error message "Cannot group that selection."

After some googling, I realised this was due to there being blanks present in my data.

A solution proposed by various people is to insert dummy values on top of the blanks and filter them out.

I would rather not do this, as the data is used for other Pivot reports - so dear SuperUsers, does anyone know of another workaround to this problem?


In the past, I've added an entire extra field to the data to act as dummy values. I can't tell if you're opposed to adding fields or just opposed to editing the data that's already there. If you add a field with an IF function that either pulls in the value or puts in something to ignore instead of blanks, you could then add that field to the pivot table and group by that. It would probably look something like:


You would replace the 0 with whatever you want to use instead of blanks.

  • As you recommended, I added an extra column/field of dummy values, as I did not want to edit existing data. My data also included "TBC" as well as blank cells, so I used an OR function as below: =IF(OR([Field]="", [Field]="TBC"), 0, [Field]) Thanks! – zek19 Jan 13 '15 at 11:53
  • Looks great. If it all worked out for you, mark the question as answered so other users with the same problem can find it more easily. – Engineer Toast Jan 18 '15 at 5:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.