Forum Discussion
Field grouping/grouping not working in pivot table
You still have texts in the source table
Most probably values were returned by formula like =IF(1,number,""). Other words cells are not blank, they have empty string value. Even if in filter they are mentioned as Blanks
If select filter table on "blanks" only, press Del (other words clean such cells), refresh PivotTable, we will see that (blank) appears in it and grouping is available
Result is
In the source table of your sample file there was mix of numbers and texts. If keep only numbers grouping works
And it's better to switch off Show Formulas flag on ribbon.
In general it's doesn't matter the value was added to the cell manually or by formula.
Thanks.
Yes that can be a problem it seems. And yes there is text in the example file. Maybe that was a bad example, because if i delete text from cells the problem still exist.
I added a new example file without text. It would be nice if you can find the fix needed! Thanks!
Regards,
Michel
You still have texts in the source table
Most probably values were returned by formula like =IF(1,number,""). Other words cells are not blank, they have empty string value. Even if in filter they are mentioned as Blanks
If select filter table on "blanks" only, press Del (other words clean such cells), refresh PivotTable, we will see that (blank) appears in it and grouping is available
Result is
- Thanks for the ellobarate answer.
It works indeed in the example file. In my real file (which i rather not share) it is not working though. And this would anyway be a step too much, since i like the table running without interference with every step. Takes away whole point of using Excel in first place 😕
I guess there's not much preventive to do about this? This kind of problem - of not rightly recognizing data - happens quite regular in someway or other.For this concrete case that's functionality which we have. PivotTable groups only numbers, and if the source is mix of texts and numbers PivotTable interprets it as texts. Thus we shall clean the source before aggregate by PivotTable. The workaround could be Power Query the source, transform and return cleaned data to Excel sheet, pivot resulting table. It shall work without manual intervention.
SergeiBaklan Make sure that you do not check the Add to Data Model checkbox. This will allow your data to be grouped.
Actually the question was not why grouping is not available, but why it doesn't work as expected.
