Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.
Following are the possible causes –
1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.
Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:
2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.
3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.
To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone
4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.
5. Edit on 11-Oct-19 – If Filter by color doesn't appear and the worksheet is compatible (>=2007), you play with the column and do some random coloring, or real – at some point it starts showing the menu item.
Credit – Raymond Hilary
Only your number 4 paragraph above solved my problem – a million thank you's as nothing else was working!!!!
# 4 AWESOME. WORKED IMMEDIATELY
Had to leave a comment for #4, good read!
Yes its # 4 , relief !!!!
5) If Filter by color doesn't appear and the worksheet is compatible (>=2007), you play with the column and do some random coloring, or real – at some point it starts showing the menu item.
Thanks for this. I will update the post.
None of these solved my "Filter by Color" being grayed out. What did work was to highlight the whole worksheet, then turn on the "Filter" and the "Filter by Color" was active. What I was doing previously was only highlighting the 1st row and then turning on the Filter which seemed to be causing the grayed out "Filter by Color".
Thanks. This only worked for me too! Have a nice day.
4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.
This is an actual thing, even with Excel 2016. The workaround didn't work, though. Luckily I had less than 20,000 records, so I just reversed the sort order and Filter by Color was enabled, finding my 3 dups.
Thanks!
I'm extremely pleased to find this great site. I want to to thank you for ones time for this wonderful read!! I definitely liked every part of it and i also have you bookmarked to see new information in your blog.