To count cells by color in Excel, filter the range by the color you want, then count the visible rows with a helper column and SUBTOTAL. For a reusable count of manually colored cells, you can also use a small VBA function. Excel has no built-in worksheet function that directly counts cells by fill color, so choose a method based on whether you need a quick count, an automatic-looking result, or support for conditional formatting.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows for the steps and VBA example below. The filter method also works in many Excel for the web workbooks, although VBA functions do not run in the web app.
- A worksheet with the cells you want to count, and a clear idea of whether the color is a manually applied fill or a color shown by conditional formatting.
- For the filter method, a helper column with a nonblank value for every row you want to count. For the VBA method, save a copy of the workbook first and save the working file as an Excel Macro-Enabled Workbook (.xlsm).
Step-by-Step
- Check how the color was applied. Click one of the colored cells and look at Home > Fill Color to see whether it has a manually applied fill. If the color changes because of a rule, select the cell and use Home > Conditional Formatting > Manage Rules to inspect the rules. The distinction matters: the VBA function below reads the cell’s regular fill color, not the color displayed by a conditional-formatting rule. If the color represents a condition such as overdue status, counting the underlying status or date is usually more dependable than counting its appearance.
- Arrange the data as one continuous list. Put headers in the first row and avoid blank rows or columns inside the list. For example, you might have a task description in column A, a permanent row ID in column B, and color-coded notes in column C. The ID should be filled in for every row, including rows where the cell you are interested in is blank. This gives SUBTOTAL something consistent to count after filtering.
- Turn on filtering. Click a cell in the data list, then choose Data > Filter. Excel adds a dropdown arrow to each header. If the data is already an Excel table, its header filters are normally available without this step. Check that the filter applies to the complete list; filtering only one column of a wider list can separate values from their original rows.
- Filter for the color. Open the dropdown on the column containing the colored cells, point to Filter by Color, and choose the fill color to count. If you are counting font color rather than fill color, choose the matching font-color option when it is available. The worksheet now shows only rows that match the selected color. If Filter by Color does not appear, check that the chosen column actually contains cell formatting Excel can filter, and that the filter covers the whole list.
- Count the visible rows with a helper column. With permanent row IDs in B2:B100 and the list filtered, enter this formula in an empty cell outside the filtered list:
=SUBTOTAL(103,B2:B100). It returns 4 if four visible rows have nonblank IDs. The 103 option counts nonblank visible cells and ignores rows hidden by the filter. Keep the formula outside the list so it does not get hidden or included in the filter. - Interpret the count correctly. The result is a count of matching visible rows with a nonblank helper ID, not necessarily a count of every colored cell in a multi-column area. If one row contains three colored cells but you filtered just one column, that row contributes one to the result. For a straightforward colored list with one relevant cell per row, the row count is the desired cell count. Record the result if you need it, then clear the filter with Data > Clear or the column dropdown’s clear-filter command.
- Use a VBA function if you need to compare manual fills repeatedly. In desktop Excel, press Alt+F11 on Windows to open the Visual Basic Editor, then choose Insert > Module and paste the code below. It compares each cell’s regular fill color with the fill of a sample cell. The example counts matching cells, including blank cells that have the same fill. On Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro > Visual Basic Editor; the keyboard shortcut can differ by keyboard and Excel version.
With the VBA function, the range to count in A2:A20 and the sample color in D1, enter this formula in a worksheet cell: =CountByColor(A2:A20,D1). If six cells in A2:A20 have the same manual fill as D1, the result is 6.
Public Function CountByColor(ByVal dataRange As Range, ByVal colorCell As Range) As Long
Dim cell As Range
Dim targetColor As Long
Application.Volatile True
targetColor = colorCell.Cells(1, 1).Interior.Color
For Each cell In dataRange.Cells
If cell.Interior.Color = targetColor Then
CountByColor = CountByColor + 1
End If
Next cell
End Function
Save the file as .xlsm and enable macros only if you trust the workbook and its code. The function uses Interior.Color, so it does not count a color that appears only because of conditional formatting. Formatting changes do not reliably trigger a worksheet recalculation; after changing fills, select Formulas > Calculate Now or edit the formula to refresh the result. Application.Volatile asks Excel to recalculate the function during calculation, but does not make a color change itself a calculation event.
Common Mistakes
- Expecting COUNTIF to read fill color. COUNTIF counts cell values that meet a condition; it does not inspect formatting. A formula such as COUNTIF(range, color) will not count cells by their fill.
- Using an incomplete helper column. SUBTOTAL with option 103 ignores blanks. If some visible rows have blank IDs, they will not be included. Fill the helper column for every data row, or use a different consistently nonblank field.
- Counting hidden rows accidentally. COUNT and COUNTA do not respond to filters in the same way as SUBTOTAL. Use the 103 option in the example when your goal is to count nonblank cells only in visible rows.
- Confusing fill color with font color. A cell can have a colored background, colored text, or both. Filter and compare the specific formatting type you mean; a font color is not the same as a fill.
- Assuming conditional formatting is a manual fill. A cell that looks red may have no red Interior.Color at all if a rule paints it red. Use a filter by color if Excel offers the displayed color, or count the data condition that drives the rule instead of relying on the VBA function.
- Including the result cell in the filtered list. Keep the SUBTOTAL formula outside the data range. Otherwise, it may be hidden by the filter or interfere with the list and its totals.
Tips
- Use a permanent ID or helper value such as a sequence number rather than a description that might be blank. The helper value does not have to be meaningful; it only needs to be present for each row.
- For repeated reporting, turn the list into an Excel table with Insert > Table. Tables make it easier to keep headers, records, and filters together as rows are added.
- If color indicates a category, add a category column such as Approved, Review, or Hold. You can then count the category with standard formulas and avoid depending on formatting. With category labels in C2:C100 and the label to count in E2, a suitable formula is
=COUNTIF(C2:C100,E2). If E2 contains Review and there are 7 Review entries, the result is 7. - For a one-time count, filtering is often safer than adding VBA. It keeps the original values intact and lets you verify which records were included before noting the subtotal.
- VBA is useful for cells with manually assigned colors, but it makes the workbook macro-enabled and less convenient to share. Recipients need desktop Excel with macros permitted for the function to calculate.
Troubleshooting
The filter shows no matching rows. Confirm the selected color is from the correct column and formatting type. Similar-looking colors can have different exact fill values. If colors come from conditional formatting, check the rule and try filtering by the displayed color where that option is available. Also verify that the filter range includes all the data rows.
SUBTOTAL returns zero or a smaller number than expected. Inspect the helper column in the rows that remain visible. Option 103 counts nonblank cells; it will not count empty helper cells. Remove any extra filters that may be hiding additional matches, and confirm the selected color filter is still active.
The VBA formula shows #NAME? or does not calculate. Check that the code is in a standard module, the function name in the formula matches CountByColor, macros are enabled, and the workbook is saved as .xlsm. Excel for the web cannot run VBA UDFs; open the file in desktop Excel instead. If the workbook is in manual calculation mode, use Formulas > Calculate Now after editing the color or formula.
The VBA count disagrees with what you see. The function compares manual cell fills, including matching fills on blank cells. It does not evaluate conditional-formatting display colors, and it does not count font color. Confirm that the sample cell has the exact fill you intend to match and that the counted range excludes unrelated cells.
The Mac steps or shortcuts differ. Ribbon labels and the Visual Basic Editor are similar in current desktop Excel for Mac, but keyboard shortcuts can vary, and some keyboards require the Fn key. Use the Developer tab or Tools > Macro to open the editor if the Windows shortcut does not work. Excel for the web does not support running VBA code.
Frequently Asked Questions
Can I count colored cells with a normal Excel formula?
Not directly. Standard worksheet functions count values and conditions, not fill formatting. Filter by color and count visible rows, use VBA for manually filled cells, or count the underlying category or condition.
Can COUNTIF count cells with a particular color?
No. COUNTIF can count a value such as Review or a number that meets a criterion, but it cannot use a cell’s fill color as its criterion. Store the category as data if you want a formula-based count.
Does the VBA function count conditional-formatting colors?
No. The code reads Interior.Color, which is the cell’s regular fill. For conditional formatting, count the condition behind the rule or use Excel’s color filter if it is available for the displayed formatting.
Will the VBA count update as soon as I change a fill?
Not necessarily. A formatting change may not trigger recalculation. Use Formulas > Calculate Now or edit the formula to refresh it. Application.Volatile does not detect formatting changes by itself.
Can I count colored blank cells?
Yes, the VBA function counts blank cells in its range if their manual fill matches the sample cell. The filter-and-SUBTOTAL method counts rows, so use a nonblank helper ID to include rows even when the color-coded cell itself is blank.
Can I use this in Excel for the web?
You can use filtering for a quick count if the workbook’s filter options include Filter by Color. The VBA function requires desktop Excel and cannot run in Excel for the web.
Related
- Color Code Data Validation in Excel with Conditional Rules
- Excel Screen Lock: How to Unlock a Sheet or Unfreeze Panes
- Close Version History in Excel Without Losing Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting for Overdue Dates in Excel: GuideIn Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting > New…
- How to Remove Formatting Marks From a Word DocumentTo hide formatting marks in Word, select Home > Paragraph > Show/Hide ¶, or press Ctrl+Shift+8 on Windows…
- Can You Paste in Reverse in Excel? Methods That WorkNo—Excel does not have a built-in Reverse Paste command that reverses copied cells as you paste them. To…
- Unmerge Cells in Google Sheets: Shortcut and StepsGoogle Sheets has no dedicated one-keystroke shortcut for unmerging cells. In a browser, open the Tool finder with…
- Excel Group Duplicate Rows Without Losing DataTo group duplicate rows in Excel, first decide what makes two rows duplicates, then sort by those columns…
- How to Import Contacts in Outlook From ExcelTo import contacts from Excel into Outlook, arrange each person on a separate row, save the worksheet as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.