Fix the PivotTable Top 10 Filter in Excel for Mac
Question details
The user needs an Excel PivotTable to display the top 100 items ranked by value, but the Top 10 filter is not working as expected.

- Product
- Microsoft Excel for Mac, version 16.34
- Device & OS
- macOS
- Scenario
- Apply a Top 10 value filter with the item count changed to 100, then display the results in descending value order.
- Observed behavior
- The PivotTable Top 10 filter does not return the expected results. Related reports describe partial filtering in a Mac workbook containing approximately 600,000 source rows.
Identify the PivotTable row field you want to rank and the value field used for ranking, such as Sum of Sales. The Top 10 filter ranks aggregated PivotTable items, not individual source rows, so confirm that this matches your goal.
Configure the PivotTable value filter to show the top 100 items
Use this first to confirm that the filter is ranking the correct field by the correct measure.
Click inside the PivotTable, then select PivotTable Analyze > Refresh to update it from the current source data.
Open the drop-down for the row field you want to rank, then choose Value Filters > Top 10. If several row fields are present, select the intended field before applying the filter.
In the Top 10 Filter dialog, select Top, replace 10 with 100, and select Items rather than Percent or Sum. Choose the value field you want to rank by, such as Sum of Sales, then click OK.
Select a numeric cell in the ranking value column, then use Data > Sort to sort from largest to smallest. Verify that the sort applies to the intended PivotTable field.

Test a smaller dataset and check whether source sorting helps
Use this when the filter settings are correct but a large workbook still returns incomplete or unexpected results.
Update Excel for Mac and report a reproducible filter problem
Use this if the Top 10 filter still behaves incorrectly after checking its settings and testing a smaller dataset.
Explore WPS Spreadsheets for Excel-compatible data analysis
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel file formats, making migration straightforward. It does not repair Excel for Mac’s Top 10 filter; test your workbook’s PivotTables and advanced features before switching.

Frequently Asked Questions
Can the Top 10 filter show the top 100 instead?
Yes. Open the PivotTable row-field drop-down, select Value Filters > Top 10, replace 10 with 100, and choose Items. Select the value field to rank by, then click OK.
Why does my top-100 filter not return exactly 100 rows?
The filter selects PivotTable items based on aggregated values, not individual source rows. Ties at the cutoff may include extra items, while fewer eligible items may return fewer results. Subtotals and grand totals also affect the number of visible worksheet rows.
Does filtering the top 100 also sort them from highest to lowest?
Do not rely on the filter to set the display order. After applying it, select a cell in the ranking value column and use Data > Sort to sort from largest to smallest.
Do I need to sort the source data before applying the filter?
Normally, no. A PivotTable value filter uses aggregated results regardless of source-row order. If a large workbook behaves incorrectly, test source sorting in a separate copy, refresh the PivotTable, and check whether it changes the result.
Is this a confirmed limitation of Excel for Mac with large datasets?
No confirmed limit is established by this report. A related case involved approximately 600,000 source rows, but that alone does not prove a general Mac limitation. Test a smaller dataset, check Help > Check for Updates, and submit feedback if the problem is reproducible.




