Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Derived Columns in Pivot Table sa02000 Excel Discussion (Misc queries) 1 February 8th 06 08:18 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Using a Pivot Table Calculated Field to get a Unique Count Mike Struckman Excel Worksheet Functions 1 November 22nd 05 06:32 PM
Pivot -- want to use Max and Sum in same table Dave Excel Worksheet Functions 1 July 13th 05 04:37 AM
how to delete/clean out the row list in pivot table john² Excel Worksheet Functions 1 May 26th 05 04:56 AM


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"