Function Problems

Fix Excel for Mac Filter Not Showing All Rows

John Wilson Oct 1, 2026 869 views

Question details

The user is experiencing an issue where the AutoFilter in Excel for Mac does not display all rows or omits the 'Blanks' option in large datasets.

Fix Excel for Mac Filter Not Showing All Rows

Product: Microsoft Excel for Mac
Device & OS: macOS Sonoma 14.4.1
Scenario: Filtering a large worksheet containing more than 10,000 unique values, where some rows have blank cells in specific columns but contain data elsewhere.
Observed behavior: Excel limits the AutoFilter results, failing to show all rows, and the 'Blanks' option is missing from the filter dropdown menu.

Before you start

Ensure your dataset does not contain completely empty rows that might break the automatic filter range, and save a backup of your workbook before applying bulk sorting or filtering changes.

Solution 1Recommended

Reset Filters and Manually Select the Data Range

Clear existing filters and reapply them to a manually highlighted range to ensure the entire dataset is included.

Clear existing filters

Navigate to the 'Data' tab on the Excel ribbon and click the 'Filter' button to remove any currently applied filters from your headers.

Select the entire dataset

Click the first cell of your data, hold 'Shift', and click the last cell (or use Command + A) to manually highlight all rows and columns you want to filter.

Reapply the filter

With the entire range highlighted, click the 'Filter' button again in the 'Data' tab. Check the dropdowns to see if all rows are now included.

Reset Filters and Manually Select the Data Range

Prevent Range Cut-offs: Manually selecting the range ensures that unexpected blank rows do not prematurely cut off the AutoFilter selection area.

Solution 2

Bypass the 10,000 Unique Values Limitation

Use the search function or custom filters to find data that exceeds Excel's visual dropdown limit.

Free Microsoft Office alternative

Use WPS Office to Manage Large Datasets on Mac

If you are frustrated by Excel for Mac's 10,000 unique value filter limits or bugs, try WPS Office. It provides a lightweight, highly compatible alternative for Mac users to filter, analyze, and manage large spreadsheets efficiently.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats without data loss.Efficiently handles large datasets and complex filtering without omitting rows.Free to use with a lightweight installation and familiar user interface.Seamless migration for Mac users experiencing Office limitations.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel for Mac not showing the 'Blanks' option in my filter?

Excel has a known limitation where it only displays up to 10,000 unique values in the filter drop-down. If your blank cell appears after 10,000 unique items, or due to a specific Mac bug, the 'Blanks' checkbox may not be generated in the visual list.

Does copying the data to a new sheet fix the filter issue?

Usually, no. If the issue is caused by the 10,000 unique values limit or an outdated Excel version, simply copying the data to a new sheet will reproduce the exact same filtering behavior.

How do I force Excel to filter a column with more than 10,000 items?

Instead of scrolling through the filter checklist, use the Search box within the filter menu to find your specific data, or apply a Custom Filter (e.g., 'Equals' or 'Contains') to bypass the visual checklist limit.

Why does my filter cut off halfway down the spreadsheet?

This happens if there is a completely blank row in your data. When you click 'Filter' without selecting a range, Excel assumes the data stops at the first empty row. Manually select your entire data range before clicking the Filter button to include all rows.

Excel & Data Problems