Table of Contents
To filter the top 10 values in an Excel Pivot Table, you can use the “Top 10” filter option in the Pivot Table menu. This will allow you to select only the top 10 items based on a certain criteria, such as the highest sales or the highest percentage of total sales. The filter will be applied to all the other fields in the Pivot Table, allowing you to easily view the top 10 items. Additionally, you can customize the filter to show the top or bottom 10 items, or the top or bottom N items.
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:
