Have you ever gone through a spreadsheet highlighting cells that need attention and then found that your list is so long it was still hard to located those cells even though they were bright yellow or red? There is an easy solution. You can filter your list to show you only selected colors.
Filtering, or Auto-Filter as it used to be called, is an incredibly useful feature in Excel. It lets you filter a list to show only the lines you want. In other words, it hides all of the rows that don’t match the criteria you choose and then brings them back when you turn the filter off. This tip is specifically about filtering by color. If you aren’t already familiar with the Filter feature, I would suggest you read my blog post AutoFilter in Excel. It was written for older versions of Excel but the information still applies.
To turn on filtering, click anywhere in the table you want to filter (if there are empty rows or columns you may need to highlight the whole area). Click the Sort & Filter button on the Home ribbon and choose Filter. Now you’ll see a button with a down arrow at the top of each column. Click the button for the column by which you want to filter your list and choose Filter by Color. A sub-menu will open. Depending on whether you have applied color to the backgrounds or to the text, the sub-menu will show Filter by Cell Color and/or Filter by Font Color. Each color that you have used will show in the menu. Just choose the one you want to see. If you are filtering by cell color there is also a choice for “No Fill”, that will show you all cells that do not have a color applied to them. When you are ready to see everything again, just go back to button at the top of the column and choose Clear Filter.
I don’t know of a way to filter for multiple colors at once, but you can sort by color. And when sorting by color you get to choose the sort order so you could put the desired colors at the top of your list.
If you are wondering how you might put this feature to work for you, think about using conditional formatting to automatically apply colors to cells that meet certain criteria. Or when comparing two lists, use the Go To command to highlight differences and then filter by color to see only those lines.
