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 have the option to filter values based on the font color and cell color.

This option is already available in the filter, you don’t have to activate anything for this 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 have used the red font color to apply the filter. And it has filter values that have 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. Click “Filter” in the Sort & Filter group if filters have not already been applied. 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. And in the below example, we have used the yellow cell color to filter values.

When you open the “Filter by Color”, you have the option to 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.
Checks 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.

Or, you can also use the keyboard shortcut Alt > D > F > F to remove the filter.

Notes

You can’t apply a filter based on cell color and font color at the same time on a single color. One type of filter can be used at a time.
You can apply a filter on multiple columns based on font and cell color. For example, you can filter one column based on the red font color and filter 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 will need to be filtered separately unless they are exactly the same color code.