Search Contextures Sites ![]()
Excel Pivot Table Filters - Top 10
Using Excel Pivot Table Top 10 Filters
Filter a Pivot Table for Top 10 Items
Modify a Top 10 Filter
Filter a Pivot Table for Bottom 10 Items
Clear an Excel Pivot Table Value Filter
Filter a Pivot Table for Top 10 Percent
Filter a Pivot Table for Bottom Sum
Pivot Table Tutorial ListView the steps in a short Pivot Table Top 10 Filters video
Using Excel Pivot Table Top 10 Filters
You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
For example, insteading of showing the total sales for all products, use a filter to show just the top 10 products, or narrow it down to the top 2.
In the example shown at the right, the City field has been filtered to show only the top 2 cities, with the highest sales amounts.
Or, if you want to focus on the poor performers, you can use a value filter to find the bottom 5 products or sales regions.
Filter a Pivot Table for Top 10 Items
In the example shown at the right, there are 24 months of Order dates in the Row Labels area. In the Values area, you can see the total sales for the first few order dates.
To filter the pivot table, so it shows only the Top 10 order dates, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, leave the default settings as is (Top 10 Items Sales):
![]()
- Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.
As shown in the filtered example at the right, only the top 10 Order Dates are now visible, still sorted by order date.
- To sort the amounts in descending order, right-click on one of the amounts, and click Sort.
- Then click Sort Largest to Smallest.
Modify a Top 10 Filter
After you add a Top 10 Filter, you can change it, to show a different result.
- In the Pivot Table, click the drop down arrow in the OrderDate field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the number of Items to 5.
- Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.
The results change, to show only the 5 order dates with the highest sales amounts.
Filter a Pivot Table for Bottom 10 Items
The Top 10 filter helps you find the highest amounts, but sometimes you need to find the lowest amounts, to focus on those. Even though the filter is named "Top 10", you can use it to find the bottom amounts too.
To see only the Bottom 10 order dates, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, select Bottom from the first drop down.
- Click OK, to close the Top 10 Filter dialog box, and apply the Value Filter.
The results change, to show only the 5 order dates with the lowest sales amounts.
Clear an Excel Pivot Table Value Filter
When you've finished analyzing the filtered data, you can clear the Value Filter, to see all the data again.
Filter a Pivot Table for Top 10 Percent
In addition to filtering for the top or bottom items, you can use a Value Filter to show a specific portion of the grand total amount. In the screenshot shown at the right, you can see that the grand total sales amount is 663,732.
Ten percent of the grand total is 66,373, and you can use a Top 10 filter to find the top or bottom dates combine to total at least that amount.
To see only the top selling order dates that contribute to 10% of the total sales amount, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the settings to:
Top 10 Percent Sales.The results change, to show only the top 2 order dates, because their combined sales are greater than 10% of the original grand total amount.
Filter a Pivot Table for the Bottom Sum
Another way to use the Top 10 Value Filter is to find the items that make up a specific sum. For example, from the order dates with the lowest sales amounts, which order dates would combine to total at least 100,000 in sales.
To see only the bottom selling order dates that contribute to 100,000 of the total sales, follow these steps.
- In the Pivot Table, click the drop down arrow in the OrderDate field heading.
- In the pop-up menu, click Value Filters, then click Top 10.
- In the Top 10 Filter dialog box, change the settings to:
Bottom 100000 Sum Sales.
The results change, to show only the 6 lowest order dates, because their combined sales are at least 100,000.
As you can see in the pivot table shown at the right, the bottom 5 order dates only total 98,165, so the 6th lowest order date is also included in the Value Filter results, achieve our 100,000 minimum.
![]()
View the steps in a short Pivot Table Top 10 Filters video
Contextures Inc., Copyright ©2011
All rights reserved.
Last updated: March 5, 2011