Grouping Numbers in Pivots - Dialog Box Not Appearing

drewmcg

New Member
Joined
Aug 10, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Do you have the field in either to Row or the Column position of the pivot table ?
Are ALL the values numeric or blank ?
If ANY values are text this will prevent the grouping dialogue box appearing, this includes any formula returning a result of "".
 
Upvote 0
The field is in the row and values fields. All values are numeric but some cells are blank.
 
Upvote 0
Blank as in empty cell should be fine.
The field you want to group by needs to be in the Row Field.
What happens when you click on either the heading or any cell in that column then right click and select "Group" ?
You should get a box that looks something like this:-
Which should allow you to do what you need.
If you get an error message it would indicate that you have text in the data or a formula is returning "".

1630485729562.png
 
Upvote 0
but the group selection dialog box will not appear
Does anything appear - eg a message saying "Cannot group that selection" or similar? Or does nothing happen at all?
 
Upvote 0
Blank as in empty cell should be fine.
The field you want to group by needs to be in the Row Field.
What happens when you click on either the heading or any cell in that column then right click and select "Group" ?
You should get a box that looks something like this:-
Which should allow you to do what you need.
If you get an error message it would indicate that you have text in the data or a formula is returning "".

View attachment 46049
When I make a selection and click group, it groups them but does not show a dialog box to set a range automatically.
 
Upvote 0
Does anything appear - eg a message saying "Cannot group that selection" or similar? Or does nothing happen at all?
No error message and no dialog box. All it does is groups the items I have selected together.
 
Upvote 0
it does is groups the items I have selected together.

Ungroup what you have done so far.
• Click in any cell of the Grouped column and Ungroup
• Then in a "single" Cell of the field you are trying to group, right click and select group.
Do you now get the dialogue box on an error message ?
 
Upvote 0
Ungroup what you have done so far.
• Click in any cell of the Grouped column and Ungroup
• Then in a "single" Cell of the field you are trying to group, right click and select group.
Do you now get the dialogue box on an error message ?
When I select a single cell and click group, I now get the error message. What could be causing this?
 
Upvote 0
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

1630496707444.png
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top