Count Cells by Color in Excel: Methods and Their Limits

How-to basics · Updated November 18, 2026

Excel has no built-in worksheet function that reliably counts cells by fill color alone. For a quick count, filter the range by color and use SUBTOTAL on a column that has a value in every row; if the color comes from a rule, count the rule’s underlying values instead. A small VBA function can count manually applied fill colors in desktop Excel, with important refresh and compatibility limits.

Why color is difficult to count

Excel treats a cell’s value and its appearance as different things. Functions such as COUNT and COUNTIF evaluate values, not ordinary formatting choices such as a yellow fill applied with the paint bucket. Consequently, a formula cannot simply ask how many cells look yellow unless you add a separate way to identify that formatting.

Color can also come from two different sources. A user may apply a fill directly, or a conditional formatting rule may display a fill when a value meets a condition. These cases need different approaches: direct formatting can be filtered or inspected with VBA, while conditional formatting is usually best counted by testing the condition that produces the color.

There is another subtlety: two cells may look identical but have different underlying colors, or may look different because of a theme or conditional rule. A count based on the stored fill color is not always the same as a count based on what appears on screen.

Choose a counting method

Use the method that matches the reason the cells have color. The simplest and most reliable choice is normally the one that counts the data rule rather than the visual formatting.

  • Color was applied manually and you need a quick one-time answer: filter by that color and count visible, nonblank rows.
  • Color represents a status or threshold: use COUNTIF or COUNTIFS to count the status or values that trigger the formatting.
  • You need a repeatable count of manually filled cells: use a VBA user-defined function in desktop Excel, and refresh it after changing fills.
  • The color comes from conditional formatting: count the rule’s logic. A VBA worksheet function that reads displayed conditional formatting is not a dependable workaround.

For a report used by other people, consider adding a Status or Category column and coloring from that data. A visible label is easier to audit, filter, and count than color alone, and it remains meaningful when someone prints the sheet in grayscale or uses a different display theme.

Quick count with Filter by Color

This method is practical when you want a quick result from cells with manually applied fill colors. It does not require a special function or a macro.

  1. Make sure the data has a header row and is arranged in a contiguous range. Select a cell in the data, then choose Data > Filter.
  2. Open the filter arrow for the column whose cells are colored.
  3. Choose Filter by Color, then select the fill color to count. Excel hides rows that do not match.
  4. Count the visible rows using a column that is filled for every data row, such as an ID column.

For example, with a header in row 1, IDs in B2:B100, and a filter applied to the records, enter this in an empty cell:

With an ID or other nonblank value in every data row in B2:B100, use =SUBTOTAL(103,B2:B100). If seven matching rows are visible, the result is 7. The 103 option counts nonblank visible cells while excluding rows hidden by the filter and rows hidden manually.

Do not count a column that may contain blanks: SUBTOTAL counts nonblank cells, not colored rows as such. If you have no consistently filled column, add a temporary helper column with a value on every record, apply the color filter, and count that helper column. Clear the filter afterward to restore the full list.

On Windows, the steps use the Microsoft 365 ribbon. Excel for Mac has filtering and color-filtering options too, though menus and dialogs can look different. Excel for the web does not run VBA, so filtering is a useful no-macro option there when the filter menu offers the needed color.

Count the condition behind a color

If conditional formatting colors a cell because it contains a particular status, count the status itself. This is more robust than counting the displayed fill: changing a theme or editing the formatting rule will not break the count as long as the data condition stays the same.

For example, suppose status labels are in D2:D100 and the conditional formatting rule colors cells containing the word Late. With those labels in D2:D100, use =COUNTIF(D2:D100,"Late"). If there are 12 cells whose value is exactly Late, the formula returns 12. The spelling and spaces in the criterion need to match the cell values.

You can count numerical thresholds in the same way. With scores in C2:C100 and red formatting applied to zero or negative scores, use =COUNTIF(C2:C100,"<=0"). If the rule is more complicated, translate its logic carefully into a counting formula; COUNTIFS can combine multiple criteria. For instance, a rule requiring both a status and a date range may need more than one condition. Keep the formatting rule and the count formula consistent so they do not silently describe different groups.

COUNTIF counts cell values, not whether the formatting rule is currently enabled or whether another rule overrides its displayed color. If rules overlap, check the rule order and “Stop If True” behavior in the Conditional Formatting Rules Manager. A formula that counts the underlying condition may include cells whose final displayed appearance is different because another rule takes precedence.

Use a VBA function for manually applied fills

If the cells have direct fills and you need a reusable count, a VBA user-defined function can compare each cell’s stored Interior.Color with a sample cell. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code below into the module. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained.

Function CountByFill(DataRange As Range, ColorCell As Range) As Long
    Application.Volatile

    Dim c As Range
    Dim targetColor As Long
    Dim total As Long

    targetColor = ColorCell.Cells(1, 1).Interior.Color

    For Each c In DataRange.Cells
        If c.Interior.Color = targetColor Then
            total = total + 1
        End If
    Next c

    CountByFill = total
End Function

With manually colored cells in A2:A100 and a sample cell in D1 filled with the color to match, enter =CountByFill(A2:A100,D1) in a worksheet cell. If nine cells in the range have the same stored fill color as D1, the result is 9. The sample cell’s fill is the reference; it does not matter what value D1 contains.

This function counts direct cell fills. It does not count a color merely because conditional formatting displays it. Application.Volatile asks Excel to recalculate the function when calculation occurs, but changing a cell’s fill is a formatting action and does not reliably trigger worksheet recalculation. After recoloring cells, recalculate the workbook or re-enter the function if its result has not updated. The function also adds a macro dependency: recipients must open the file in desktop Excel and allow macros according to their organization’s security settings. Excel for the web cannot run VBA, and Mac desktop Excel supports VBA but its editor and keyboard setup can differ.

Before adding or running VBA, save a separate copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and the user-defined function depends on the code remaining in the workbook.

Conditional formatting and displayed colors

A common question is whether VBA can count the color that conditional formatting visibly paints. The distinction matters: a cell’s ordinary Interior.Color describes its underlying fill, while conditional formatting can change its displayed appearance. A worksheet user-defined function should not be treated as a reliable way to retrieve that displayed conditional format. In particular, using DisplayFormat in a worksheet UDF is not a dependable solution.

Whenever possible, use the same value test that the conditional formatting rule uses. If the rule is based on a formula, reproduce the logic in a count formula or create a helper column that marks qualifying rows. For complicated rules, a helper column makes the decision visible and easier to check; then you can count its TRUE results or labels. If the rule depends on a formula referencing other cells, verify that the helper logic uses the same references and relative/absolute reference behavior.

When the format is genuinely the only record of the category—for example, someone manually colored a legacy schedule and the original labels are gone—filtering or a VBA procedure may be necessary. Consider converting the colors into explicit text labels before building a permanent report. That preserves the meaning instead of relying on a visual convention.

Common problems and checks

  • The result is too small: a SUBTOTAL formula may be counting a column with blanks. Use an ID column populated on every row.
  • The color filter shows unexpected records: check whether the chosen color is a cell fill or a font color, and confirm the filter is on the intended column.
  • The VBA count does not change after recoloring: formatting changes may not trigger calculation. Recalculate or re-enter the function, and confirm the workbook is macro-enabled.
  • Cells look the same but are counted differently: the fills may use different theme or RGB colors, or one appearance may come from conditional formatting rather than a direct fill.
  • A formula count disagrees with the visible color: inspect conditional formatting rules, overlapping rules, and the exact criteria used in the formula.

For an auditable result, spot-check several matching and nonmatching cells. Also note whether the count refers to direct fills, displayed conditional formatting, or rows meeting a data condition. Those are distinct definitions of “by color,” and documenting which one you used prevents confusion when the workbook changes hands.

Frequently Asked Questions

Can Excel count cells by color without VBA?
Yes, for a quick count: filter the data by fill color and use SUBTOTAL on a column with a value in every row. For conditional formatting, count the underlying condition with COUNTIF or COUNTIFS. Excel has no standard COUNTBYCOLOR worksheet function.

Can COUNTIF count a fill color?
No. COUNTIF evaluates cell contents against criteria; it does not inspect ordinary fill formatting. If the fill represents a value such as Late or a threshold such as zero or below, use COUNTIF on that value or threshold instead.

Will the VBA function count conditional formatting colors?
The function shown counts direct fills through Interior.Color. It does not reliably count the color displayed by conditional formatting. Count the rule’s logic or use a helper column for that case.

Why does my color count not update when I change a fill?
Changing formatting does not reliably trigger recalculation of a worksheet function. Recalculate or re-enter a VBA-based formula, and remember that Excel for the web cannot run VBA.

Can I count font color instead of cell fill?
Filtering and the VBA example here concern cell fill color, not font color. For font color, use a method specifically designed to inspect font formatting, or preferably count the data category that the font color represents.

What is the best method for a recurring report?
Store the category or status in a data column, apply conditional formatting from that value, and count the value with ordinary Excel formulas. This is easier to maintain and works without macros.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.