Top 5 plus Other in Pivot table

[almpk]

Hello!
I'd have a pivot table that contains 10 customer names. At any time,
different customers make up the Top5 revenue earners. I'd like the pivot
table to show me the top 5 based on revenue (which I can make it do), and
then I'd like to see an OTHER line that groups all the others together to get
a TOTAL.

Right now, if I ask it to do top 5, it only shows the top 5 and their sum.

Is there any way to do this?
Thank you in advance!
Megan

 

[Tom Hutchins]

Yes, it can be done.

1. Don't use the "Top 10 Autoshow" feature on the Field Settings menu.
Instead, sort the Revenue column in descending order.

2. Select the bottom 5 customers (all fields). From the Pivot Table toolbar,
select Pivot Table >> Group and Show Detail >> Group. A new field is created
inthe pivot table (in my test table, it is called Customer2). A dummy
customer is created called Group1.

3. With the Group1 customer selected, from the Pivot Table toolbar, select
Pivot Table >> Group and Show Detail >> Hide Detail.

4. Select the pivot table cell that says Group1 and press F2. Replace Group1
with OTHER.

5. Drag the old Customer field (to the right of the Customer2 field) off the
pivot table into limbo.

6. With any of the Customer2 cells selected, from the Pivot Table toolbar,
select Pivot Table >> Field Settings. Change the field name "Customer2" to
"Customer "(that's Customer followed by a space.)

If your pivot table is the same size and location each month, you could
record yourself performing the above steps, and with minimal editing, create
a macro to semi-automate the process in the future.

Hope this helps,

Hutch