Do you have colored cells that need to be filtered?

You are probably already filtering based on the cell values but that same filtering can be based on the cell color as well.

For example, you may have a table of data and decide to add a fill color format. You could color each cell based on the cell content and then filter by the color!

This post will show you all the ways to filter by color in Excel.

Filter by Color from Filter Toggles

The most common way to filter data in Excel is through the filter toggles. Once you enable the data filter you can filter by color with its built-in menu.

Follow these steps to filter by color.

1. Select your table header cells.

2. Under the Data tab toggle the Filter menu button. The filter toggles will appear on your headers.

💡 Tip: You can quickly apply the filter toggles to your data by using the Ctrl + Shift + L keyboard shortcut. This is an easy way to toggle on or off the filters for any data in your Excel workbook.

3. Select the filter toggle for your column of colored cells.

4. Hover over the Filter by Color option and choose the color to filter based on from the submenu.

Immediately your table has been reduced to only showing rows containing green cells!

Filter by Color from the Right Click Menu

You can quickly filter rows through the right-click menu without having to manually enable the filter toggles.

To filter a single color this way:

1. Right-click on a cell whose color you’d like to filter.

2. Drill down to the Filter options.

3. Choose Filter by Selected Cell’s Color.

Notice how the filter toggles are automatically enabled and your table has been filtered to show only rows containing green cells!

Filter by Color with VBA

VBA is a programming code you can leverage to automate many Excel tasks including filtering.

You can write a VBA macro that will filter by the currently selected cell’s color:

1. Open the Visual Basic Editor by pressing Alt + F11 or going to the Developer tab and selecting Visual Basic. You may need to enable the Developer tab if it is hidden from the ribbon.

2. Select the Insert menu and choose the Module option.

Sub FilterByColor()

Dim selCell As Range
Dim color, field

Set selCell = Selection

If Intersect(selCell, ActiveSheet.UsedRange) Is Nothing Then
    'No table selected
    Exit Sub
End If

If ActiveSheet.autofilter Is Nothing Then
    'Turn on filter toggles
    selCell.autofilter
End If

field = selCell.Column - ActiveSheet.autofilter.Range.Column + 1
color = selCell.Interior.color

'Filter by color
selCell.autofilter field:=field _
    , Criteria1:=color _
    , Operator:=xlFilterCellColor

End Sub

3. Double-click the new module to open it and paste the above VBA code into that module.

The code first ensures you’ve selected within the table. selCell is the selected cell whose color you want to filter. field is the table’s field number containing the colored cells.

This example has a field number of 2 because the field that needs to be filtered is the second table column. color is the color of the selected cell.

You can then run the code.

4. Go back to your sheet and select a cell whose color you want to filter.

5. Under the View menu select Macros.

6. Select your FilterByColor macro.
7. Click the Run button.

Your table now appears filtered by the color of the cell you selected!

Filter by Color with Office Scripts

You can use Office Scripts in Excel if you have an online version of Excel under a Microsoft 365 business plan.

Microsoft also introduced Office Scripts to the recent beta version of Excel for the Desktop.

You can use Office Scripts to automate tasks like color filtering as follows.

Open your workbook that contains your table data.

1. Go to the Automate tab.
2. Select New Script.

Ensure you have a version of Excel that supports Office Scripts if you don’t see the Automate tab.

function main(workbook: ExcelScript.Workbook) {
	//Worksheet
	let selectedSheet = workbook.getActiveWorksheet();
	//Selected cell
	let cell = workbook.getActiveCell();

	if(selectedSheet.getUsedRange().getIntersection(cell)==null){
		return;
	}

	//Turn on filter toggles
	let af = selectedSheet.getAutoFilter();
	af.apply(cell);

	//Color of selected cell
	let color = cell.getFormat().getFill().getColor();

	//Column index within table
	let col = cell.getColumnIndex() - af.getRange().getColumnIndex();

	//Filter by color
	af.apply(af.getRange(), col, {filterOn: ExcelScript.FilterOn.cellColor, color: color});

}

3. Paste over the entire contents of the Code Editor pane with the above script.

The script gets the active cell and retrieves its color to determine what color to filter by.

The filter toggle af is turned on and the filtered column index is stored in the col variable. The table column index always starts at 0 so the index for the column that needs filtering is 1.

The last line uses all the info to apply the color filter.

You can then see the results of your script.

4. Select any colored cell on your sheet.

5. Click the Run button found in the Code Editor pane.

Notice that the color you selected has now been filtered in your table!

Conclusions

This post showed you several different ways to filter by color in an Excel table.

Filtering your color with the toggle filters is a good method to use when you already have filtering set up the way you like.

Going through the right-click menu is a quick alternative but be aware that Excel guesses at your table’s cell range when doing this.

You get complete control over your color filtering if your use VBA or Office Scripts and you can reliably repeat the filtering on different sheets. These are good tools if you have the supported Excel version and the programming knowledge to do it.

Have you ever needed to filter your data based on cell color? Did you know you could do this? Let me know in the comments below!