Function Problems

How to Fix Excel Filter Not Showing Blanks or All Unique Items

Maira Mehtab Sep 22, 2026 873 views

Question details

The user needs to view and filter all unique items, including blank cells, within a large dataset, but the filter dropdown menu is incomplete.

Product: Excel for the web / Excel Desktop
Device & OS: not provided
Scenario: Filtering a column that contains more than 1,000 unique values.
Observed behavior: The Excel filter dropdown omits the '(Blanks)' option and displays a warning stating that not all unique items are shown.

Before you start

Verify the approximate number of unique data points in your column to confirm whether you are hitting the 1,000-item limit of the web application.

Solution 1Recommended

Switch to the Excel Desktop Application

Use the desktop version of Excel, which supports displaying up to 10,000 unique items in the filter dropdown instead of the 1,000-item web limit.

Excel for the web is designed for lightweight editing and implements a strict display limit of 1,000 unique items in the auto-filter list to ensure browser performance. The desktop application is built to handle much larger datasets.

Open in Desktop App

In Excel for the web, look for the 'Viewing' or 'Editing' ribbon at the top of the window and click on the 'Open in Desktop App' button.

Access the Column Filter

Once the file opens in your desktop Excel program, click the filter dropdown arrow located on the header of your target column.

Select Blanks or Unique Items

Scroll to the bottom of the filter list to locate and select the '(Blanks)' option, or search for the specific unique items that were previously hidden online.

Desktop Limit: Even the desktop application has a limit. It will only display up to 10,000 unique items in the dropdown menu.

Solution 2

Use Text Filters in Excel for the Web

Bypass the visual dropdown limit by using the built-in conditional text filters to explicitly isolate blank cells.

Powerful Data Management

Process Massive Datasets Seamlessly with WPS Spreadsheet

WPS Spreadsheet is a robust desktop application that effortlessly handles heavy datasets without the restrictive web limitations. It gives you full control over extensive data filtering with ease.

1. Open Your Spreadsheet: Launch WPS Office and open your .xlsx or .csv dataset within the WPS Spreadsheet application.
2. Enable AutoFilter: Highlight your header row, navigate to the 'Data' tab on the top ribbon, and click the 'AutoFilter' button.
3. Filter Data Normally: Click the dropdown arrow on your desired column to easily view, search, and select from up to 10,000 unique items, including blank cells.

View up to 10,000 unique items in your filter dropdown menusFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Free, lightweight desktop software optimized for smooth performanceAdvanced filtering and conditional formatting tools included built-in

Frequently Asked Questions

Why does Excel say 'Not all items showing' in my filter?

This warning appears when the number of distinct values in your column exceeds the application's display limit for the filter menu. Excel for the web maxes out at 1,000 items, while the desktop version stops at 10,000 items.

How can I filter a column that has more than 10,000 unique items?

If your unique values exceed 10,000, you cannot select them via the basic checkbox list. Instead, you must use the 'Search' bar within the filter menu, apply 'Text Filters' or 'Number Filters', or utilize advanced features like Pivot Tables to analyze the data.

Are blank cells always placed at the bottom of the filter list?

Yes, by default, Excel groups all empty cells into a single '(Blanks)' option positioned at the very end of the auto-filter checkbox list, provided the total unique item count has not exceeded the software's dropdown limit.

Excel & Data Problems