'%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 Online 'Not All Unique Items Are Shown' Filter Error
Question details
The user is unable to view all unique filter items in Excel Online due to display limits, even though the desktop version works perfectly.
- Product
- Excel Online
- Device & OS
- not provided
- Scenario
- Filtering data with a large number of unique entries in Excel for the web.
- Observed behavior
- Excel Online displays a warning message stating that not all unique items in a column are shown, preventing the user from filtering the full dataset.
Verify that your dataset doesn't contain unnecessary blank rows, as these can count towards the filter item limits in the web application.
Open the Spreadsheet in Excel Desktop Application
Switching to the desktop version of Excel bypasses the web app's strict memory and filter display limits.
Excel Online is designed for browser performance, meaning it imposes lower limits on how many unique filter items it can display at once (often lower than the standard 10,000 limit). Moving the file to a desktop application resolves this instantly.
In your Excel Online browser window, navigate to the top ribbon menu where your primary tools are located.
Click on the 'Viewing' or 'Editing' dropdown button located near the top right of the ribbon.
Choose 'Open in Desktop App' from the dropdown menu to launch the full version of Excel, which supports higher unique item limits for data filtering.
Hide Older or Unused Rows to Reduce Filter Load
Temporarily reducing the number of active rows helps Excel Online process and display the remaining unique items in the filter dropdown.
Filter Massive Datasets Without Limits Using WPS Office
If Excel Online's limitations are slowing you down, try WPS Office. It's a free, lightweight desktop alternative that fully supports Microsoft Excel formats without the restrictive browser-based filtering limits.
- 1. Download and Install WPS Office: Download the free WPS Office suite from the official website and install it on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx file directly. The software automatically recognizes standard Excel formatting.
- 3. Apply Unrestricted Filters: Select your header row, go to the 'Data' tab, and click 'Filter' to view all unique items without web app limitations.
Frequently Asked Questions
Why does Excel Online say not all unique items are shown?
Excel for the web has built-in limits on the number of unique items it can load in a filter dropdown. This restriction is designed to optimize browser performance and limit memory usage, which differs from the heavier desktop version.
What is the maximum filter limit in Excel?
The desktop version of Microsoft Excel can display up to 10,000 unique items in a filter dropdown. Excel Online, however, relies on dynamic limits based on server and browser capabilities, frequently capping well below the desktop threshold.
Can I bypass the Excel Online filter limit without installing the Microsoft desktop app?
Yes. If you don't have the MS Excel desktop app, you can bypass this web restriction by downloading a free desktop alternative like WPS Office to open the file, or you can temporarily hide older rows in Excel Online to reduce the dataset size.
