"Others" category in Pivot Tables

Hi,

If your categories are in column A, and the numbers are in column B, and the cutoff number to put in OTHER is say less than 10, then-
Add a new column and put in a formula like this:

=IF (B2<10,"OTHER",A2)

and copy down.

Now you can make a pivot table using categries in the new column.

Eli

I interpreted your question differently. If you want to use the "Other" basket for individual elements < x, then Eli's response is what you're after. If you are looking to use the "Other" basket after everything has been summed up into the pivot table then you can manually highlight those rows you wish to consolidate, right-click and pick Group and Show Detail | Group... Overwrite the default name (Group1 or something like it) to "Other" and then double-click to collapse.

Greg Truby said:

I interpreted your question differently. If you want to use the "Other" basket for individual elements < x, then Eli's response is what you're after. If you are looking to use the "Other" basket after everything has been summed up into the pivot table then you can manually highlight those rows you wish to consolidate, right-click and pick Group and Show Detail | Group... Overwrite the default name (Group1 or something like it) to "Other" and then double-click to collapse.

Click to expand...

Greg,

Elegant!

Never new you can do this.

It is obviously the right way.

Eli

Thanks for the help to both of you!
Greg, I tried the grouping method as you suggest but for me, an error box pops up saying that I cannot group that selection. BTW I am trying to group rows. If I try to group columns, that's OK, but I need to group rows.

Thanks

Hmmm, should work in most instances. Though I do recall a case where Excel got cantankerous with me on this same issue - I just can't recall the specifics... If you can use COLO's utility to post your PT and perhaps a snippet of your source data we can take a look and see what Excel's bellyachin' about.

FYI - If you have data with dates that you want to group, Excel will give you a popup asking how you want it grouped - weeks, months, quarters, etc. If you go for anything less than a year and you have more than a year's worth of data, click Year AND your other group level. Else Excel will group Jan 2002 with Jan 2003 - which normally is a bad thing.

OK, I see what I was doing. I was selecting just one item, although it selected more than one row (I have sub-categories) and trying to group those. Excel complained, but its error message of "Can't group that selection"" could, I think, have been a little more helpful.

Hey-ho, you live and learn. Thanks to you both, works great now.