Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5 plus Other in Pivot table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5 plus Other in Pivot table
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 "almpk" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions |