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.
Sort by Sales Values
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.
Top 3 Sales in Pivot Table
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.
Find Best Results
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, you can watch this short Excel video tutorial.
________________
Do you know how can we lock the filter? The thing is that when I refresh the pivot table, the filter disappears.
Thanks!