Filter is not Showing All the Values in Excel

Last Updated: February 16, 2024
puneet-gogia-excel-champs

- Written by Puneet

Why Filter Doesn’t Show All the Values

Reason: The filter does not show you all the values because it is limited to showing you the first 10000 unique values. If the data in the columns where you have a filter has more than 10000 unique values, it will show you a message (Not all items are showing) in the filter drop-down.

When you click on this message, it shows you a message box with the message (This column has more than 10,000 unique items. Only the first 10,000 unique items are displayed).

Using a similar example, we have a list of numbers up to 11000. When you scroll down to the last value in the list in the filter, it shows you 10000.

But when you use the search bar for a number above 10000, say 10500, it shows you that number to apply/remove the filter from it.

And the same thing happens when you have more than 10000 unique text values. In the example below, you can see that we have a list of 49993 unique names, and when you open the filter, it only shows you the first 10000 names in the filter list.

Solution to this Problem

This is not a bug but a limitation in Excel. You can use the other filter options available directly in the filter drop-down to make it work.

Use the Search Bar to get the values you want to filter

When you open the filter, you have a search bar and can enter the values there to get those values to filter.

You can also use the Custom Filter options to filter values using a condition.

For example, lower or greater value, value with a specific text, etc. Below is a snapshot of custom filters available for text values.