Grouping Numbers in Pivots - Dialog Box Not Appearing

[drewmcg]

Hello,

I am trying to group numbers in a pivot table by ranges (e.g. <0.5 - 0.5 - 0.9, 1.0 - 1.9, 2.0 - 2.9 etc) but the group selection dialog box will not appear to allow me to set the parameters automatically.

If I continue to do it manually, a new number not currently registered will disrupt the table when the data is refreshed.

Can anybody offer any advice?

Best,

Drew

 

[drewmcg]

If you go to the column heading of the Field you are trying to group and click on the filter drop down, go right to the bottom, do you see text values (this could be obvious text or numbers that seem out of order) and / or do you see a check box with nothing next to it ?
The blank is ok but the rest are non-numeric data that needs to be fixed, so that the Pivot recognises the field as being numeric and allows the grouping function.

Rough example

View attachment 46057

I don't seem to have any non-numeric data appearing in the column. I am totally stumped!

 

[Alex Blakenburg]

Are you able to share that worksheet via dropbox, onedrive etc ? (post a link and set the permissions to allow anyone with the link to open it)
You can blank out all the other data as long as you leave that column unchanged
If that column is a calculation it will mean you will need to include any columns that it is uses to calculate the value.
Unfortunately it is quite late here in Australia and I won't be back online until tomorrow (technically it is already tomorrow here)

PS: One other thing to check, although all the amounts are ticked, you don't have a value or label filter on that column that is excluding the text values do you ?

 

[RoryA]

Did you add the data to the data model?

 

[drewmcg]

Thanks for your help guys. I've managed to create an =IF formula do do the ranges and then make a pivot table based off that.

 

[Alex Blakenburg]

Glad you found a solution. Let us know if it did turn out to be some text values in the column.

 

[CassaundraC]

These message boards have saved me so many times. After following the above instructions I found a simple solution and wanted to share.

Once you have done the following as shared above:

1. Options > Data > Ensure box next to "Disable automatic grouping of Date/Time Columns in PivotTables" is checked
2. Make sure the date column on original sheet is formatted as date and not text
(If not use text to columns to convert and regenerate the pivot table)

Do the following:

3. In the actual pivot table highlight the dates you want to group and right click
4. Select Format Cells
5. Select Date
6. Select the 3/14/12 options under type (the group box will not open if you have 03/14/12 or 3/14/2012)
7. Return to table, highlight and right click to select group

8. THE BOX APPEARS !!!!