How to Filter by Color in Excel

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 the red font color to apply the filter, so it shows only the values that use red as the font color. Here’s how to filter a column by color:

1. Apply Filter: The first thing you need to do is to apply a filter to the column. For this, you can use the keyboard shortcut (Alt > D > F > F, or Ctrl + Shift + L), or go to Data > Sort & Filter > Filter.
2. Open the Filter: You’ll see drop-down arrows in each column header. Click the drop-down arrow of the column you want to filter.
3. 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.
4. 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 “Filter by Color”, you can also filter the cells that don’t have any cell color (No Fill) or font color (Automatic).

Filter by Color from Right-Click Menu

The right-click menu doesn’t have the same “Filter by Color” list. However, it lets you filter the column 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.

1. 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.
2. Right-click on it.
3. Go to the Filter option.
4. 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 column A (A1:A100) based on the cell color of the cell you select.

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 (Operator:=xlFilterCellColor). To filter by font color instead, use myRange.Cells(1, 1).Font.Color and Operator:=xlFilterFontColor.

Clear Filters

On the Ribbon, navigate to the Data tab. Click the “Clear” button in the Sort & Filter group (Alt > A > C). 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 completely, with the drop-downs, using the keyboard shortcut Alt > D > F > F.

Notes

• You can’t filter one column by cell color and font color at the same time. Only one color filter can be used on a column 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.
• Filter by Color also works with colors from conditional formatting, so you can filter the cells that a rule has highlighted.
• Remember that filtering by color matches the exact color, so it treats different shades or tints as different colors. If you have similar colors, they must be filtered separately unless they are exactly the same color code.

Sum or Count the Filtered Colored Cells

Once you filter by a color, you can sum or count only the visible cells with SUBTOTAL. The following formula sums the visible cells in B2:B100, so it gives you the total of the color you have filtered:

=SUBTOTAL(109,B2:B100)

And to count the visible cells that have a value, use 103 instead:

=SUBTOTAL(103,B2:B100)

Sort by Color

If you want to see all the colored cells together without hiding the other rows, sort by color instead. Open the filter drop-down, go to Sort by Color, and pick the cell color or font color you want on top. You can also use Data ➜ Sort and choose Cell Color or Font Color in the Sort On box to sort by more than one color.

Frequently Asked Questions