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:
Step 3: Filter Top 10 Values in Pivot Table
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 tasks 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