Did you know that you can filter by color since Excel 2007? Yes, that’s almost 10 years. I wont’ say it is a new feature notwithstanding the fact that many people are not aware of it yet. 😛
Filter by Color is nice and easy to use, which I think there is no argument.
Interestingly, the Filter by Color may not be working properly if we are working with a very large table. What I mean by “large” here is a table with a column of more than 10001 unique items.
The following demonstrates the situation:
The first filled color appears on row 10003, which is the 10002th unique item on column B. Surprisingly, Excel fails to detect the filled color and hence does not offer me the Filter by Color.
(Note: If the filled color starts on the 10001st unique item, it works normally… @_@)
By default (or limitation), Excel only displays the first 10,000 unique items in filter.
I am not sure if it is the reason for Excel’s failure in detecting the filled color that falls on/after the 10,002nd cell.
Here’s a a not-so-perfect workaround…
- Go back to the very top of the list
- Apply the filled color that you need to filter (tip: copy and paste format is safer in order to get the filled color of interest)
- The Filter by Color is back. Yeah!
I experienced this in Excel 2010. I didn’t test it on other Excel versions. Please let me know if it is not an issue in Excel 2013 or 2016.
Also please share with us if you have other solutions or workarounds by leaving comment.