How to Count Highlighted Cells in Excel

Excel has no built-in function that counts cells by fill color, but you can get the number in seconds with Find: press Ctrl + F, click Options > Format, choose the fill color, and click Find All. The count appears at the bottom of the dialog. For a number that stays on the sheet, filter the column by color and use =SUBTOTAL(103,A2:A500), or add a small VBA function. If the color comes from conditional formatting, count the rule’s condition with COUNTIF instead.

Method 1: Find All (quickest)

1. Select the range to count, or one cell to search the whole sheet.
2. Press Ctrl + F and click Options >>.
3. Click the arrow next to Format and choose Choose Format From Cell, then click a highlighted cell.
4. Leave Find what empty and click Find All.

The bottom of the dialog shows something like “12 cell(s) found”. Press Ctrl + A while the results list is active to select them all. The status bar then also shows Count, Sum and Average for those cells. Click Format > Clear Find Format when you’re done.

Choose Format From Cell also picks up fonts and borders. If some highlighted cells aren’t found, use Format instead and set only the Fill color.

Method 2: Filter by color and SUBTOTAL

1. Click a cell in your data and press Ctrl + Shift + L to add filter arrows.
2. Below the data, in an empty cell, type =SUBTOTAL(103,A2:A500), adjusting the range to your column.
3. Click the filter arrow on the column, choose Filter by Color, and pick the fill color.

SUBTOTAL with 103 counts only visible, non-empty cells, so the result is the number of highlighted rows. Use =SUBTOTAL(109,B2:B500) to sum a number column for the same rows. Clear the filter and the formula shows the full count again. More on SUBTOTAL in how to count the number of rows in Excel.

Method 3: A VBA function

If you want a formula like =CountColor(A2:A500,D1) that counts cells matching the color of D1, add this function. It needs a macro-enabled workbook (.xlsm).

1. Press Alt + F11 to open the VBA editor.
2. Choose Insert > Module.
3. Paste the code below, then close the editor.

Function CountColor(rng As Range, sample As Range) As Long
    Dim c As Range
    Application.Volatile
    For Each c In rng
        If c.Interior.Color = sample.Interior.Color Then
            CountColor = CountColor + 1
        End If
    Next c
End Function

Type =CountColor(A2:A500,D1) in any cell, where D1 has the color you want to count. Changing a cell’s color doesn’t trigger a recalculation, so press F9 after recoloring cells. Save the file as Excel Macro-Enabled Workbook.

To sum instead of count, change the line inside the loop to add c.Value rather than 1, and change the function type to Double.

Cells colored by conditional formatting

None of the methods above see conditional formatting colors reliably: Find and the VBA function read the cell’s own fill, not the rule’s. The easy fix is to count the rule’s condition directly. If a rule highlights values over 100, use =COUNTIF(A2:A500,">100"). For rules based on text, use =COUNTIF(A2:A500,"*overdue*"). Filter by Color does work with conditional formatting colors, so Method 2 is fine too.

See how to make a cell turn a color with a formula and how to change cell color based on text for setting up those rules.

Which method should you use?

Situation	Method
One-off count	Find All
Count that updates with a filter	Filter by Color + SUBTOTAL
Formula you can copy and reuse	VBA CountColor
Colors from conditional formatting	COUNTIF on the same condition
Workbook can’t contain macros	Find All or Filter + SUBTOTAL

Troubleshooting

Find All finds nothing

Two shades that look the same can be different colors. Use Choose Format From Cell on an actual highlighted cell rather than picking from the palette.

#NAME? after adding the VBA function

Macros are disabled or the code is in the wrong place. Put it in a standard module (Insert > Module), not in a sheet’s code window, and enable macros when the file opens.

The SUBTOTAL count doesn’t change

The range doesn’t match the filtered column, or the cells are empty. SUBTOTAL 103 skips blanks, so count a column that always has a value.

Frequently asked questions

Does Excel for the web support this?

Filter by Color and SUBTOTAL work in Excel for the web. VBA doesn’t run there, and Find has fewer format options.

Can I count cells by font color?

Yes. In Find, set the font color under Format > Font. In VBA, replace Interior.Color with Font.Color.

Summary

• Quick count: Ctrl + F > Format > Find All.
• Live count: Filter by Color with =SUBTOTAL(103,range).
• Reusable formula: a CountColor VBA function.
• Conditional formatting: COUNTIF the same condition.

Related: hide rows based on a cell value in Excel.

Related: how to color cells in Excel.