Incorrect sort order on Pivot Table

I have a Pivot Table where the row labels are numeric values between 0 and 150. I have decided to group them by 5s (right click row entry choose Group from the popup menu).

whenever I do this, the sort order gets confused. When they are ungrouped it is correct and the rows of the pivot table are sorted correctly, because it treats the entries as numbers so it's sorting them numerically. Then they are grouped, the row entries contain text strings, for example:

0-4
5-9
10-14

etc.

So for values over 100, it ends up showing like this:


0-4
5-9
10-14
105-109
145-150
15-19

etc. Is there a way to make this work correctly without resorting to manual sorting?

Err, that was embarassing. Not sure what happened there. Thanks for your patience anyway

Try the attached workbook.

Originally Posted by teylyn

One of the ground rules of pivot tables is that the data source should not have blank rows.

Hmm, well since the data comes from a constantly growing database to which I am regularly adding new rows, how do I address the issue of not wanting to go maintain the named range which represents the actual database every single time I add more rows, but still allow the Pivot Table to automatically pick up the newly entered data?

Is there a way to, like, define a named range where the bounds of the range are conditional? So like I have the main database which contains blank rows, and then define another range which always contains the subset of the larger range which has everything up until the first blank row?

This doesn't seem like too strange of a use case.

1. Ungroup your data
2. Sort Ascending or Descending
3. Go to More Sort Options
4. Change to Manual and click ok again
5. Regroup how you want your data