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.

pivottop10filter00

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.

pivottop10filter01

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.

pivottop10filter01b     

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

pivottop10filter01c

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

pivottop10filter02

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.

pivottop10filter03

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.

pivottop10filter04

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.

pivottop10filter05

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.

________________

You may also like...

1 Response

  1. Ignacio Bayot says:

    Do you know how can we lock the filter? The thing is that when I refresh the pivot table, the filter disappears.

    Thanks!

Leave a Reply

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