Why can’t I filter by color in Excel?

Excel’s filtering capabilities are extremely useful for quickly organizing and finding the data you need. However, one limitation is that you cannot directly filter by cell fill color or font color in the default Excel filters. So if you have data formatted with different text or fill colors, you cannot simply filter the cells by that color.

In this article, we’ll explain the reasons why Excel doesn’t allow color filtering, and provide some workarounds that allow you to filter your data by color.

How Excel’s filters work

To understand why Excel doesn’t support color filtering, it helps to know a bit about how Excel filter functionality is programmed. When you enable filtering on a data range in Excel, it adds drop-down arrows to the top row that allow sorting and filtering by the values in each column. Filtering is done by searching for cells that match (or don’t match) the values you specify in the filter criteria.

But cell colors aren’t considered values. The color is simply a display format, while the underlying value is what Excel searches when filtering. For example, if you have the text “Apples” colored red and “Oranges” colored orange, the filter sees those as text strings, not as colors. So there is no way to tell the normal Excel filters to show only cells formatted with a certain color.

Color formatting is separate from values

This gets to another reason why Excel’s filters ignore colors – cell formats like color, fonts, borders are kept separately from the values themselves. The color formatting is not part of the value. When you filter, you are filtering the values in the cells.

Think of it this way – say you copied a formatted data set from Excel and pasted it into Notepad. You would lose all the colors, fonts, borders etc. But the data values would still be there. The colors are not part of the data itself.

So in order to filter by color, Excel would need an entirely separate filtering system that looked at the color formatting rather than the values. And it would need some way to apply both value and color criteria at the same time, which would significantly complicate the filter interfaces.

Alternatives for filtering by color

Now that we have explained why Excel filtering cannot directly use color, here are some suggested workarounds that will effectively let you filter data based on cell colors or font colors:

Use conditional formatting icons

One option is to apply conditional formatting with icon sets, which displays icons directly in the cells based on color rules. These icons will show up in the filter dropdowns, allowing you to filter by color that way.

For example, you could apply green and red icon sets to color code cells. Then filtering the icons would let you show only the green or red cells.

Add a helper column with color labels

Another approach is to add a new helper column, and use formulas to return a text label for the color. For example, “Red”, “Blue”, “Green” and so on. You can then filter this column to show only rows with a certain color label.

Here is an example where column B contains cell colors, and column C uses formulas that convert the color to a text label that can be filtered:

A	B	C
Apple		Red
Banana		Yellow
Blueberry		Blue

The formula in C2 would be: =IF(B2="red","Red",IF(B2="blue","Blue","Yellow"))

And then you can filter column C to only show rows where the color label equals “Red”, “Blue” etc.

Use VBA macros to filter by color

Finally, it is possible to filter by color in VBA macros. VBA code can loop through all cells, check their color formatting, and show/hide rows based on the detected colors. This takes more effort than the previous options, but gives the most flexibility.

Here is some sample VBA code that will filter the data to only show rows where cell B contains the color red:

Sub FilterByColor()

  Dim r as Range
  For Each r in Range("B1:B100") 
    If r.Interior.ColorIndex  3 Then  
      r.EntireRow.Hidden = True 
    Else
      r.EntireRow.Hidden = False
    End If
  Next r

End Sub

As you can see, VBA provides direct access to check and act on the color formatting of cells. So it can apply a color filter without needing a separate helper column.

Add-ins and other worksheets with color filtering

Some third party Excel add-ins provide more advanced filtering options including by font color, fill color or cell color. For example Kutools for Excel has a Filter by Font/Cell Color option.

And other spreadsheet apps like Google Sheets, Smartsheet and Airtable have built-in options to filter views by cell or font color.

So if filtering data by color is important in your work, switching to another spreadsheet tool is an option worth considering.

Summary

Filtering by color is a commonly requested feature in Excel, but unfortunately not provided with the default filters. This is due to how Excel separates formatting like colors from the underlying values that the filters actually search.

But with a bit of workaround such as conditional formatting, helper columns or VBA macros, you can still get the effect of color-based filtering. This allows you to filter Excel sheets based on cell fill colors or font colors.

While color filtering is not innately supported, hopefully these suggestions give you some options to get similar functionality. Let me know if you have any other creative ideas for filtering by color in Excel!