Count Green Cells in Excel: 3 Reliable Ways to Do It

How-to basics · Updated November 20, 2026

To count green-filled cells in Excel, select the range and use Find with a fill format, filter the column by color and count visible entries, or use a VBA function if you need to count blank green cells too. The best method depends on whether the cells are manually filled or colored by conditional formatting, and whether blank cells should be included.

What You’ll Need

  • A copy of the workbook or a clear idea of the range you want to count.
  • A green cell to use as a visual reference. If the sheet uses several greens, decide which exact shade counts.
  • For the filter method, a column with a header and one record per row. For the VBA method, a desktop version of Excel that can run macros; Excel for the web cannot run VBA.

You do not need an add-in for the Find or filter methods. If your green cells are produced by conditional formatting, identify the rule that applies the green fill before choosing a method. A cell’s displayed color may not be its actual, manually applied fill.

Step-by-Step

  1. Choose the exact range and color. Note the cells to include, such as A2:A100, and check whether there are multiple green shades. Excel treats different fill colors as different formats. Also decide whether blank green cells matter: the Find and VBA methods can include them, while a visible-cell count based on nonblank entries cannot.
  2. Method 1: Find cells with a matching fill. Select the range to search. On the Home tab, choose Find & Select > Find, or press Ctrl+F on Windows. Select Options to show the full Find dialog, then choose Format > Format. In the Format Cells dialog, open Fill and choose the specific green under Background Color. Select OK to close each dialog and return to Find All. This searches for the selected fill format rather than cell text.
  3. Read the Find All results. Choose Find All and inspect the results list and the dialog’s found-cell count. The search is limited to the range you selected; if you did not select a range, Excel may search the current sheet. If the number seems wrong, confirm the Fill color in the search format and check that no cells outside the intended range were included. Find is a good quick choice when you need a one-time count and want blank green cells included.
  4. Method 2: Filter a table by fill color. Use this when each colored cell represents a row or record, such as a green status cell in column A. Make sure the data has a header row, then click inside it and choose Data > Filter. Open the dropdown on the column that contains the green fills, choose Filter by Color, and select the green shade. Excel hides rows that do not match. This method filters records; it does not directly count every green cell across multiple columns.
  5. Count the filtered entries. If every relevant row has a nonblank value in the filtered column, select the values below the header and read Count on the status bar. If Count is not shown, right-click the status bar and enable Count. For a reusable count in a worksheet cell, with the green-filled entries in A2:A100 and that same column filtered by color, enter =SUBTOTAL(103,A2:A100). It returns the number of visible, nonblank cells in that range. Clear the filter afterward with Data > Clear or the column dropdown’s Clear Filter option.
  6. Method 3: Count manually filled green cells with VBA. Use this when you need blank green cells included or want a count in a worksheet cell. On Windows, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module. Paste the function below into the module. Save a copy of the workbook first; VBA changes cannot be undone with Ctrl+Z. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and enable macros only if you trust the file.
  7. Enter the function in the sheet. With manually filled cells to count in A2:A100 and a sample cell D1 filled with the exact green shade, enter =CountFillColor(A2:A100,D1) in an unused worksheet cell. The result includes blank cells in A2:A100 if their fill matches D1. Change the range or sample-cell reference to suit your layout. Keep the sample cell outside the count range to avoid confusion.
Function CountFillColor(rng As Range, sample As Range) As Long
    Dim cell As Range
    Dim total As Long

    Application.Volatile
    For Each cell In rng.Cells
        If cell.Interior.Color = sample.Interior.Color Then
            total = total + 1
        End If
    Next cell

    CountFillColor = total
End Function

On Mac, open the Visual Basic Editor from the Developer tab’s Visual Basic button; the keyboard shortcut can vary with the keyboard and macOS settings. Excel for the web does not run VBA, so use Find or a filter in a supported desktop version instead. This function compares manually applied cell fills. It does not reliably count the displayed color created by conditional formatting.

If a green fill comes from a conditional formatting rule, count the condition rather than the visible color. For example, if the rule colors values of 10 or more green, with the values in A2:A100 and that rule applying to the same range, use =COUNTIF(A2:A100,">=10"). This returns the number of values meeting that condition. For a rule with several criteria, make the count formula reflect the same criteria and ranges as the rule; use COUNTIFS when the rule depends on multiple conditions. If the rule uses a formula, reproduce its logic in a suitable count formula rather than relying on the displayed fill.

Common Mistakes

  • Counting the wrong shade. Similar-looking greens can have different color values, especially if one is a theme color or tint. Find and the VBA function match the selected format or sample color; they do not interpret shades as equivalent.
  • Assuming COUNTIF can count fill color. COUNTIF checks cell values against criteria, not ordinary cell fill formatting. It is useful when green is generated by a rule based on values, but not as a direct count of manually colored cells.
  • Forgetting blank cells. SUBTOTAL with function number 103 counts visible, nonblank cells. A green blank cell will not increase that result. Use Find or the VBA function if blanks should count.
  • Counting rows instead of cells. A filter on one column shows matching rows. If a row contains three green cells, a count of visible records is not a count of all three green cells.
  • Ignoring conditional formatting. The fill you see may be applied by a rule rather than stored as a manual fill. A fill-based search or VBA check can then disagree with the displayed color. Check the rule in Home > Conditional Formatting > Manage Rules.
  • Leaving an old filter or Find format in place. A previous filter can hide rows, and a saved Find format can affect later searches. Clear filters and use Find’s Format dropdown to clear the search format before starting a fresh count.

Tips

  • Keep one sample cell filled with the exact shade you use and label it clearly. It makes the VBA method easier to maintain and helps you compare colors visually.
  • For recurring work, make the data an Excel table with clear headers. Filters are easier to apply and new rows are easier to include than in a manually selected block.
  • After changing a cell’s fill, recalculate the VBA result with F9 on Windows. Formatting changes alone do not always trigger recalculation of a worksheet function. On Mac, use the equivalent recalculation command for your Excel keyboard settings or choose Formulas > Calculate Now.
  • Use Find All for a quick audit, then inspect a few matches to confirm they are in the intended part of the sheet. A count is only useful if the search range and color definition are right.
  • If the fill color is just a visual status for a value such as Approved or Complete, consider counting the status text instead. Text-based counts are easier to refresh and less likely to break when someone changes formatting.

Troubleshooting

Find reports no matches. Select the intended cells again and open the Find dialog’s Format settings. Choose the fill from the Fill tab, not just a cell’s text, and confirm the shade is exact. If the cells are colored by conditional formatting, use a formula based on the rule instead; Find may not match the displayed result as expected.

The filter count is too small. Check whether some matching rows have blank cells in the column being counted. SUBTOTAL with 103 ignores blank cells, even when their rows are visible. Count a different column that is always populated, or use Find or VBA if the task is to count colored blanks.

The VBA formula shows #NAME? or does not update. Confirm the function is in a standard module, the workbook is saved as .xlsm, and macros are enabled for this trusted file. Check the spelling of CountFillColor and the range and sample references. Press F9 after changing fills. If the displayed green comes from conditional formatting, this function is not suitable; count the rule’s criteria instead.

The result differs from what you can see. Check for hidden rows, different green shades, cells outside the intended range, or conditional formatting rules. Also confirm whether your method counts visible records, nonblank cells, or every matching cell, since those are different totals.

Frequently Asked Questions

Can I count green cells with a normal Excel formula?
Not by fill color alone: standard worksheet functions such as COUNTIF do not test ordinary cell formatting. Use Find, filter and count visible entries, or a VBA function. If the color represents a condition, count the condition itself.

Does the VBA function count blank green cells?
Yes. It checks the fill of every cell in the specified range, whether or not the cell contains a value. Make sure the sample cell has the exact fill you want to count.

Will the VBA function count conditional formatting colors?
No. The function compares the manually applied fill. For a conditional format, create a count formula that mirrors the rule’s logic, or use Find only after confirming that its format search matches the cells you need.

Can I count green cells in Excel for the web?
You can use Find or filter options available in the web interface, but Excel for the web cannot run VBA. For a VBA count, open the macro-enabled workbook in desktop Excel.

Why does SUBTOTAL return zero after filtering by green?
It may be counting a range that is blank in the visible rows, or the filter may not have selected the intended shade. Use a populated column in the SUBTOTAL range and confirm the filter color. Remember that function number 103 counts visible, nonblank cells—not blank colored cells.

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.