Aug 09 2020 05:15 AM - edited Aug 09 2020 05:17 AM
Hi,
I spend some hours trying to bundle data in groupings in my table. Still no result.
The "field grouping" option in the menu of pivot table analysis is greyed out for some reason. Thats the one i need (based on a video i saw).
In short i look for a way to get the count of grouping 1-5 (e.g.) etc.
I attached an example file. Would be nice if someone can find the solution. Tia!
Regards,
Michel
Aug 09 2020 05:24 AM
Aug 09 2020 06:19 AM
Hi
Thanks. Saw these before.
I found that the grouping function is not working with data comes from a formula (in my main table).
But when i copy this output in a new table, without formulas, i am able to make a pivot table and do a manual grouping.
Problem is that this is not how i like it to be. I want to be able to use the original input data of course and not copy paste this all te the time...
Guess it is quite hard to find the reasons why this is made this way...
Aug 09 2020 12:54 PM
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.
Aug 09 2020 11:30 PM
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
Aug 10 2020 02:58 AM
SolutionYou 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
Aug 10 2020 06:37 AM
Aug 12 2020 10:39 AM
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.
Aug 10 2020 02:58 AM
SolutionYou 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