Compare Top and Bottom Sales in Pivot Table

An Excel pivot table is a great way to summarize a large amount of data, and with its Top 10 filter, you can compare the top values to the bottom values. But don't limit yourself to the Top 10 versus the Bottom 10 – dig deeper by using the other options in the filter.

Summarize the Data

With a few mouse clicks, you can summarize thousands of rows of data into a concise and informative pivot table. In this example, there is a list of product, and their total sales over two years.


Instead of viewing the products alphabetically, you can sort by total sales, in descending order, to see the best selling products at the top of the list. Here is the same list, with Oatmeal Raisin at the top.


Spotlight the Best Selling Products

Instead of showing all the products, you can use the pivot table's Top 10 filter in the Product field, to filter the results.


The Top 10 filter is customizable, and can be used to show the top 3 items, instead of the top 10.


Here is the pivot table, with the Top 3 items showing, and the grand total for those items.


Compare to Bottom Items

If you're working on a sales plan, you might want to decide where to focus your efforts, and a pivot table, or two, could help.

  • If the top 3 products have total sales of approximately $136K, how are the bottom selling products doing, in comparison?
  • How many of those bottom selling products are required to match the top 3 sales?

To find out, you can make a copy of the pivot table, and change the Top 10 filter. Instead of Top 3 Items, filter for Bottom Sum, and use the $136K amount as the target SUM.


Difference in Comparison Results

In this example, the top 3 sales were $136,165, and the bottom 10 products have sales of $173,489. The totals are not an exact match, because the pivot table filters for the products that total the specified sum, or more.


The bottom 9 products don't reach the target amount, so the 10th lowest product is also included. That puts the total over the target, and it shows that the best results come from a small number of products.

Focus your sales efforts there, and you might have a great sales year.


Watch the Pivot Table Top 10 Compare Video

To see the steps for comparing top and bottom values in a pivot table, please watch this short Excel video tutorial.


0 thoughts on “Compare Top and Bottom Sales in Pivot Table”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.