The following step-by-step example shows how to filter for the top 10 values in an Excel pivot table.
Step 1: Enter the Data
First, let’s enter the following sales data for 15 different stores:
Step 2: Create the Pivot Table
To create a pivot table, click the Insert tab along the top ribbon and then click the PivotTable icon:
In the new window that appears, choose A1:C26 as the range and choose to place the pivot table in cell E1 of the existing worksheet:
Once you click OK, a new PivotTable Fields panel will appear on the right side of the screen.
Drag the Store field to the Rows box, then drag the Sales and Returns fields to the Values box:
The pivot table will automatically be populated with the following values:
To only display the 10 stores with the highest values in the Sum of Sales column, right click on any of the store names.
In the dropdown menu that appears, click Filter, then click Top 10:
In the new window that appears, select Top 10 Items by Sum of Sales, then click OK:
The pivot table will automatically be filtered to only show the 10 stores with the top 10 values for the Sum of Sales column:
Note: You can use a number other than 10 to filter for a different number of top values.
Step 4: Sort the Top 10 Values in Pivot Table [Optional]
Notice that the filter option displays the 10 stores with the highest sales values, but it doesn’t automatically display them in sorted order.
To sort the stores by sales values, right click on any value in the Sum of Sales column, then click Sort, then click Sort Largest to Smallest:
The stores in the pivot table will automatically be sorted from largest to smallest based on sales:
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Sort Pivot Table by Grand Total in Excel How to Group Values in Pivot Table by Range in Excel How to Group by Month and Year in Pivot Table in Excel
Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.
This Excel tutorial explains how to show the top 10 results in a pivot table in Excel 2016 [with screenshots and step-by-step instructions].
If you want to follow along with this tutorial, download the example spreadsheet.
Download Example
Steps to Show the Top 10 Results in a Pivot Table
To show the top 10 results in a pivot table in Excel 2016, you will need to do the following steps:
- Select the cell that contains the results to filter. In this example, we have selected the Order ID field which is cell A1 [we want to show the top 10 Order IDs based on the "Sum of Quantity"].
- Click on the arrow to the right of the Order ID drop down box and select Value Filters > Top 10 from the popup menu.
- When the Top 10 Filter [Order ID] window appears, select Top, 10, Items, and Sum of Quantity in the respective drop downs. Then click on the OK button.
Now when you view your pivot table, you should only see the top 10 Order IDs based on the Sum of Quantity.
How to filter top 20 in PivotTable?
Show the top or bottom 10 items.
In the PivotTable, click the arrow. ... .
Right-click an item in the selection, and then click Filter > Top 10 or Bottom 10..
In the first box, enter a number..
In the second box, pick the option you want to filter by. ... .
In the search box, you can optionally search for a particular value..
How to filter top 20 in Excel?
Filter for the top or bottom set of values Click a cell in the range or table that you want to filter. On the Data tab, select Filter. in the column that contains the content that you want to filter. Under Filter, select Choose One, and then enter your filter criteria.
How to get top 3 in PivotTable?
Summary.
Create a new pivot table on the same worksheet..
Add the Name field to the Rows area..
Add the Score field to the Values area..
Rename the Score field from "Sum of Score" to "Score " [note trailing space].
Filter values to show "Top 3 items by Score".
Set sort to "Descending by Score".