'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Filter Cannot Show Blank Cells or All Unique Items
Question details
The user is unable to filter blank cells and receives a notification that not all unique items are shown in the dropdown.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Collaboratively editing a workbook online and attempting to use column filters for blank or unique values.
- Observed behavior
- Excel fails to display all unique items in the filter dropdown and omits blank cells, despite the column having fewer than 1,110 entries.
Verify whether you are using Excel for the web in your browser or the Excel desktop application, as the web version has stricter display limitations on filter dropdown menus.
Use Text Filters to Find Blank Cells in Excel for the Web
Overcome the 1,000-item dropdown limit in Excel for the web by using custom text filters to isolate blank cells.
Excel for the web restricts the filter dropdown menu to approximately 1,000 unique items to optimize browser performance. If your data exceeds this limit, blank cells and other unique entries might not appear in the initial checklist, even if the total row count seems low.
Click the filter dropdown arrow located on the header of the column you want to filter.
Select 'Text Filters' (or 'Number Filters', depending on your data format) from the dropdown menu.
Choose the 'Equals...' option from the expanded context menu.
Leave the input value field completely empty and click 'OK' to apply the filter. This will successfully display all blank cells in that column.
Open the Workbook in the Excel Desktop App
Switch from Excel for the web to the desktop version to access significantly higher filter limitations.
Easily Manage Large Datasets with WPS Office
Avoid browser-based limitations and handle extensive spreadsheet data effortlessly with WPS Office. It provides a robust desktop experience for spreadsheets without the steep cost.
- 1. Download WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx or .xls file.
- 3. Filter Without Limits: Click the filter icon on your column headers to easily view and filter all unique items and blank cells without restrictive web limits.
Frequently Asked Questions
Why does my Excel filter say 'Not all items showing'?
This message appears because the list of unique items in your column exceeds the maximum display limit for the filter dropdown. Excel for the web is limited to 1,000 items, while the desktop version is limited to 10,000 items.
Can I increase the filter dropdown limit in Excel for the web?
No, the 1,000-item limit in Excel for the web is hardcoded to ensure browser stability and performance. It cannot be manually increased. You must use custom text filters or switch to a desktop application.
How do I filter out blanks instead of finding them?
Using the custom Text Filter menu, select the 'Does Not Equal' option and leave the input text field completely blank. Clicking OK will hide all empty cells and only display rows containing data.
