MrExcel.com - Your One Stop for Excel Tips & Solutions

Limit a Pivot Report to Show Just the Top 5 Customers

December 29, 2022 - by

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.

Open the Filter for customer. Choose Values Filters, Top 10
Figure 903. 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.

  • 3. Change 10 to 5.

The Top 10 Filter dialog lets you choose Top or Bottom, enter a number, then choose Items, Percent, or Value and then specify Sum of Revenue, Sum of COGS, or Sum of Profit. For this example, select Top 5 Items by Sum of Revenue.
Figure 904. Show top five customers.

  • 4. Click OK.

The report successfully shows the top five customers. The Grand Total is showing revenue for just those five.
Figure 905. The pivot table shows the top five customers.

Results: The report will be filtered to show just the top five customers. Note that a Filter icon appears in cell A3 to indicate that you are not seeing all customers. You can hover over this icon to see a list of the filters applied

Gotcha: If there is a tie for fifth place, the list may contain more than five customers. If you filter the pivot table to an obscure product purchased by only a few customers, you might have a hundred-way tie at $0 for fifth place.

Additional Details: Another common request might be to show enough customers to represent 80% of the total.

Another use for the Top 10 Filter: Ask for the Top, 80, Percent.
Figure 906. Show top 80%.

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.

Another use for Top 10 Filter. Ask for Top, 2 Million, Sum. This gives you enough customers to reach 2 million dollars or more. In this case, Walmart, General Motors, and Exxon total $2.3 Million.
Figure 907. 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..

This article is an excerpt from Power Excel With MrExcel

Title photo by Joshua Hoehne on Unsplash