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 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
Hi. Is there a way to get the data for this filtered top 10 count. As I double click the pivot table, I get the entire data dump and not the top 10 data.
Hi Asad…In Excel, when you filter a Pivot Table for the “Top 10” values and double-click on the Pivot Table to drill down, you indeed get the entire data source behind the Pivot Table, not just the filtered Top 10 data. Unfortunately, this is how Excel’s default behavior works. However, there are a few methods to extract only the Top 10 filtered data from your Pivot Table:
—
### **Method 1: Use the Filtered Data Directly**
1. **Apply the Top 10 Filter**:
– Right-click on the desired field in your Pivot Table.
– Choose **Filter > Top 10** and set your filter criteria.
2. **Manually Copy the Filtered Data**:
– After applying the filter, select the visible rows of the Pivot Table (i.e., the Top 10 rows).
– Copy and paste the data into a new worksheet or range for further use.
—
### **Method 2: Use Power Query**
Power Query allows you to extract only the filtered data, bypassing the need to use Pivot Table drill-down.
1. **Load Your Data into Power Query**:
– Select your data source, then go to **Data > Get & Transform Data > From Table/Range**.
2. **Apply the Top 10 Filter in Power Query**:
– In Power Query, sort the column you want to filter by in descending order.
– Use the **Keep Rows > Keep Top Rows** option from the ribbon and specify `10`.
3. **Load the Filtered Data**:
– Click **Close & Load** to load the Top 10 filtered data into a new worksheet.
—
### **Method 3: Use Advanced Filtering**
1. **Create a Helper Column**:
– In your data source, add a helper column that ranks the values using the `=RANK` function or similar.
2. **Filter the Top 10 in Your Original Data**:
– Use an advanced filter or sort the helper column to show only the Top 10 rows.
– This way, you can get the exact rows matching the Top 10.
—
### **Method 4: Use VBA for Automation**
If you frequently need to extract Top 10 data, you can use a VBA script to automate the process.
1. **Insert VBA Script**:
– Press `Alt + F11` to open the VBA editor.
– Insert a new module and paste the following code:
“`vba
Sub ExtractTop10FromPivot()
Dim pvt As PivotTable
Dim pvtField As PivotField
Dim pvtItems As PivotItem
Dim ws As Worksheet
Dim destRow As Long
Set pvt = ActiveSheet.PivotTables(1)
Set pvtField = pvt.PivotFields(“YourFieldName”) ‘Replace with your field name
‘ Create a new worksheet for the output
Set ws = Worksheets.Add
destRow = 1
‘ Loop through Top 10 items
For Each pvtItems In pvtField.PivotItems
If pvtItems.Visible Then
ws.Cells(destRow, 1).Value = pvtItems.Name
destRow = destRow + 1
End If
Next pvtItems
End Sub
“`
2. **Run the Script**:
– Replace `”YourFieldName”` with the name of the field you’re filtering on.
– Run the macro, and it will extract only the Top 10 filtered rows into a new worksheet.
—
### **Recommendation**
For a quick and efficient solution, **Method 1** is the easiest if you only need this occasionally, while **Power Query** (Method 2) is ideal for robust and repeated use cases.