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.
wmfexcel 
After struggling with the same problem for couple of hours and just before loosing my mind I’ve sorted the problematic column upside down and then it worked for me. I don’t know how and why and not sure it’ll work again.
LikeLike
I discovered that if I highlight a single cell or row, then turn Filter On, that Filter by Color does not work. If I highlight the entire sheet (you know, the little triangle near “A” & “1”, then Filter by Color works! Call me crazy, but that is what works for me.
LikeLike
So, for me, it is about setting up the filter feature correctly initially, (highlight everything before clicking on the filter icon), not about the colors or how I use the filter.
LikeLike
Here is my solution: If you activate the filter function after clicking on a single cell or a row, filter by color does not work. Instead, highlight the entire sheet (you know, the little triangle near A1), then click on the filter icon. Now, filter works (at least, for me)! Call me crazy, but it works.
LikeLike
Thanks Fred for sharing! 🙂
LikeLike
I just wanted to say THANK YOU for this article because your suggestion of highlighting the first cell in the column is the only thing that worked for me! All of a sudden I lost the ability to sort by color in a large Excel 2013 file, and I was getting frustrated that none of the other articles I found were helping. This helped right away! So, thank you very much 🙂
LikeLike
Hi TC,
You are welcome. Glad it helps! 😀
LikeLike
No, my data was not unique, that’s probably the catch. I haven’t try unique records, sorry.
LikeLike
No worry. Maybe you may test it and tell me the result later.
Cheers,
LikeLike
I have a question, actually not related to this topic. I have a table with employees and their id numbers, also there is a column with their organizational roles, in a separate column I have formula which looks up their rate from another table.
I need a formula (true or false) which will compare text in column E “Staff Role” to another table with roles and rates and if the formula won’t find the exact role it will say “No role” or something like that. Do you have any ideas what should I use?
LikeLike
I don’t think there are many formulas to compare text in a cell to text in array based on criteria.
LikeLike
When I said: “I need a formula (true or false) which will compare text “, I mean – I want to compare text in the “staff role” column to the list of all roles in another table and return Yes (the same text is on the list) or No (the same text is not on the list). But I also want to check this text (staff role description) against each employee, so the criteria would be employee id.
I think SUMIFS has its syntax similar to what I need (SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, …), the problem is that it works perfectly with numbers, but not with text. If I could somehow change “sum_range“ to “check_range“ that would be great.
LikeLike
Hi Igor,
Are you trying to count the match? If so, try COUNTIFS.
LikeLike
Hi Igor,
VLOOKUP is on top of my mind.
Nevertheless, I am not sure what do you mean by
“I need a formula (true or false) which will compare text in column E “Staff Role” to another table with roles and rates”… does the “Rates” one of your criteria?
Please try to elaborate more, ideally with a simple example, and I will see if I could assist.
Cheers,
LikeLike
Hi Fung,
Hopefully you are doing well!
I do some operations with color filter and it was interesting for me that you noticed it doesn’t work for more than 10,000 lines, so I tried it myself and found that it all works good on mu computer. I created 100,000 records and painted 15,836 of them, applied filter and works perfectly.
(I would love to insert a screenshot here but I can’t.
LikeLike
Hi Igor,
Thanks for your comments.
Do you data contain more than 10,000 unique items?
In my case, the failure seems to be a result of too many unique items on the column and the color-filled cells happened on the very last position (beyond 10,001th unique items).
Nevertheless, I haven’t tried that in other computer. So I am glad that you took the effort to test it and let us know the result.
Cheers,
LikeLike
Hi Igor,
As MF says, are any of your coloured cells on a row less than that of the 10,001st unique item? If so, then that is probably why your filter is working. It’s not that you have more than 10,000 rows or records, but where the first coloured cell is.
If not, I’d be interested in what version of Excel you have (or had, back there in 2016! 🙂 )
LikeLike