How to Filter by Color in Excel

Contents hide

Steps to Filter a Column by Color in Excel

Using a Macro Filter Data Based on the Cell Color

In Excel, when you apply a filter to a column, you can filter values based on font and cell colors. This option is already available in the filter; you don’t have to activate anything once you apply a filter.

Note – The “Filter by Color” feature is available in Excel 2007 and later versions.

Steps to Filter a Column by Color in Excel

Excel allows you to filter by cell color, font color, or conditional formatting icon.

In the above example, we used red font to apply the filter. It also has filter values that use red as a font color. Here’s how to filter a column by color:

Apply Filter: The first thing you need to do is to add an apply filter to the column. For this, you can use the keyboard shortcut (Alt > D > F > F), or go to the Data > Sort & Filter > Data.
Open the Filter: Go to the “Data” tab on the Ribbon. If filters have not already been applied, click “Filter” in the Sort & Filter group. You’ll see dropdown arrows appear in each column header.
Filter by Cell Color or Font Color: Hover over “Filter by Color” in the dropdown menu. You’ll see “Filter by Cell Color” and “Filter by Font Color”.
- Filter by Cell Color: Select this option if you want to filter based on the background color of the cells.
- Filter by Font Color: Choose this if you’re filtering based on the text color in the cells.
After selecting a color, Excel will filter the column, showing only the rows that match your color criteria. Rows with cells that don’t match the selected color will be hidden.

As I said, you have two ways to use color to filter values:

By Cell Color
By Font Color

In the same way, you can filter based on cell color. In the example below, we used the yellow cell color to filter values.

When you open the “Filter by Color”, you can filter cells where you don’t have any cell color or font color.

Filter by Color from Right-Click Menu

Excel does not have a “Filter by Color” option in the right-click menu. However, you can filter columns based on the cell color or font color of the selected cell. Let’s say you want to filter all the cells based on the cell color of the selected cell.

Select the cell on which you have the cell color or the font color you want to use to filter the values from the columns.
Right-click on it.
Go to the Filter Option.
Click on “Filter by Selected Cell’s Color”.

In the same way, you can filter the cells based on the font color of the cell.

Use the “Filter by Selected Cell’s Font Color”.

Using a Macro Filter Data Based on the Cell Color

Here’s an example of filtering the columns A1 based on the cell color of the cell you specify.

Sub FilterByColor()
    Dim myColor As Long
    Dim myRange As Range
    On Error Resume Next
    Set myRange = Application.InputBox("Select a cell to filter by its color", Type:=8)
    If Not myRange Is Nothing Then
        myColor = myRange.Cells(1, 1).Interior.Color
        ActiveSheet.Range("$A$1:$A$100").AutoFilter Field:=1, Criteria1:=myColor, Operator:=xlFilterCellColor
    End If
End Sub

Declares a Long variable myColor and a Range object myRange.
On Error Resume Next is used to handle any errors that might occur during the execution of the code. It allows the code to continue running even if an error is encountered.
Set myRange = Application.InputBox(“Select a cell to filter by its color”, Type:=8) displays an input box asking the user to select a cell. The selected cell’s range is then assigned to myRange.
Check if myRange is not Nothing, which means if a cell has been selected.
If a cell has been selected, it gets the color of the interior of the first cell in myRange and assigns it to myColor.
Applies a filter to the range A1:A100 in the active sheet, based on the color of the selected cell.

Clear Filters

On the Ribbon, navigate to the Data tab. Click the “Clear” button in the Sort & Filter group. This action will remove all the filters currently applied to your dataset but will leave the filter drop-downs in place, allowing you to reapply filters as needed.

You can also remove the filter from the keyboard shortcut Alt > D > F > F.

Notes

You can’t apply a filter based on cell color and font color to a single color at the same time. Only one type of filter can be used at a time.
You can filter multiple columns based on font and cell color. For example, you can filter one column based on the red font color and the second column based on the yellow cell color.
Ensure that the colors used for highlighting or marking cells are consistent throughout your dataset.
Remember that filtering by color does not distinguish between shades or tints. If you have similar colors, they must be filtered separately unless they are exactly the same color code.