Excel Limit a Pivot Report to Show Just the Top 5 Customers
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Limit a Pivot Report to Show Just the Top 5 Customers
Problem: Many times my customer reports have hundreds of customers. If I’m preparing a report for the senior vice president of sales, he may not care about the 400 customers who bought spare batteries this month. He wants to see only the top 10 or 20 or 5 customers each month.
Strategy: You can accommodate this vice president by using the Top 10 Filter feature that is available in pivot tables. Follow these steps:
1. Build a pivot table with Customers in the row area.
2. Open the dropdown at the top of the customer dropdown. Choose Value Filters and then Top 10.
Figure 841 The top 10 can do to or bottom, 5, 10, 20, and more.
Excel displays the Top 10 Filter (Customer) dialog. By default, the dialog wants to show the top 10 items based on Sum of Revenue. Although it is called the “Top 10" feature, it is far more flexible than that. The first dropdown offers to filter to the top or bottom customers. You can use the spin button to change 10 to any other number. The third field offers Items, Percent, and Sum.
Or, you can ask for enough customers so the sum is $2,000,000. Excel will include the largest customers until the total is over $2,000,000.
Figure 845 Show enough customers to be over $2,000,000.
Gotcha: The total on each report includes only the customers shown in that report. My VP of Sales wants the other customers grouped into one line called Other. See the next topic for an alternate strategy.
Additional Details:To clear a filter, you use the dropdown at the top of that column and select Clear Filters from Customer..