This guide shows you how to count cells in Microsoft Excel that have a specific fill color. Whether you manually colored cells or used conditional formatting, you’ll learn several methods to get an accurate count, including using the Find feature, a simple VBA macro, and the newer FILTER function. No prior programming experience is needed for the first two methods.
What You’ll Need
- Microsoft Excel 2010 or later (the FILTER function requires Excel 365 or Excel 2021)
- A worksheet with cells that have a fill color
- Basic familiarity with Excel’s ribbon and menus
Step-by-Step
Method 1: Using Find & Select (Quickest, No Formulas)
- Press Ctrl + F to open the Find and Replace dialog box.
- Click the Options button to expand the dialog.
- Click the Format button (next to the Find what field).
- Go to the Fill tab, choose the color you want to count, and click OK.
- In the main dialog, click Find All. The bottom of the dialog will show a list of all cells with that color.
- Look at the bottom-left corner of the dialog: it says “X cell(s) found”. That’s your count.
Method 2: Using a Simple VBA Macro (Works for Manually Colored Cells)
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
- Click Insert in the top menu and choose Module.
- Copy and paste the following code into the module window:
Function CountColor(rng As Range, colorCell As Range) As Long Dim cell As Range Dim targetColor As Long targetColor = colorCell.Interior.Color For Each cell In rng If cell.Interior.Color = targetColor Then CountColor = CountColor + 1 End If Next cell End Function - Close the VBA editor (Alt + Q).
- In a cell, type
=CountColor(A1:A10, B1)where A1:A10 is your range to count and B1 is a cell that has the fill color you want to count. - Press Enter. The cell shows the count.
Method 3: Using the FILTER Function (Excel 365 / 2021)
- Ensure your data is in a contiguous range.
- In a cell, enter this formula (adjust ranges and color reference as needed):
=ROWS(FILTER(A1:A10, GET.CELL(38, INDIRECT("A"&ROW(A1:A10))) = COLORINDEX(B1)))Note: This requires defining a name for GET.CELL.
- Alternatively, use a helper column: In an empty column (e.g., B), enter
=GET.CELL(38, A1)and drag down. This returns a number for the fill color. - Then use
=COUNTIF(B:B, GET.CELL(38, B1))to count cells matching the color number.
For most users, Method 1 or Method 2 will be sufficient. Method 3 is more complex and only recommended if you need a dynamic count that updates when colors change.
Common Mistakes
- Counting conditional formatting colors: The Find and VBA methods above only count manually applied fill colors. If cells are colored by conditional formatting, these methods will not see them. You need to use the conditional formatting rules themselves to count (e.g., use COUNTIF with the same condition).
- Using the wrong color reference: In VBA, the
Interior.Colorproperty returns a long integer. If you pick a color from a palette that is not exactly the same (e.g., a slightly different shade), the count will be zero. - Forgetting to enable macros: If you save as .xlsx, the VBA macro won’t be saved. Save as .xlsm (macro-enabled workbook) and enable macros when opening.
- Including header rows: Make sure your range doesn’t include a header that happens to be the same color.
Tips
- For a quick one-off count, use Find & Select. It’s the fastest and requires no formulas.
- To count multiple colors at once, repeat the Find & Select for each color, or modify the VBA to loop through an array of colors.
- If you need a permanent count that updates when you recolor cells, the VBA UDF (user-defined function) is your best bet—just remember to press F9 to recalculate if you change colors after the formula is entered.
- Use the Name Manager to define a name for the GET.CELL function if you want a cleaner formula.
Troubleshooting
- Find All shows no results: Make sure the color you selected in the Format dialog matches exactly. Use the eyedropper tool if available, or manually enter the RGB values.
- VBA function returns 0: Check that the range you passed includes the colored cells. Also verify that the colorCell reference is a single cell, not a range. If you copied the color from a cell that uses a theme color, try using
Interior.ColorIndexinstead of.Colorin the VBA code. - VBA function returns #VALUE!: This usually means the macro is not enabled or the function name conflicts with a built-in function. Make sure the module is inserted correctly and the workbook is saved as .xlsm.
- FILTER method returns #NAME?: The GET.CELL function is an old XLM macro function that requires the workbook to be saved in a macro-enabled format and the function to be used inside a defined name. Follow the exact steps for defining a name.
- Counts are wrong after sorting: If you use the VBA method, the formula will recalculate when the workbook recalculates. If it doesn’t, press Ctrl+Alt+F9 to force a full recalculation.
Frequently Asked Questions
Can I count cells colored by conditional formatting?
No, the methods above only count manually applied fill colors. To count conditional formatting colors, you need to replicate the condition in a COUNTIF formula. For example, if cells are red because they are greater than 100, use =COUNTIF(A:A,">100").
Why does my VBA function return 0 even though I have colored cells?
Most likely the color reference cell is not exactly the same color as the cells you’re counting. For example, a dark red vs. a standard red. Use the exact cell that you want to match, or use ColorIndex instead of Color in the code.
How do I count multiple colors at once?
You can use the Find & Select method for each color and add the totals. Alternatively, modify the VBA function to accept a range of color cells and loop through each, summing the counts.
Will the VBA function update automatically when I change a cell’s color?
No, not automatically. Excel does not recalculate a formula when you change a cell’s formatting. You need to press F9 or trigger a recalculation (e.g., by editing any cell) to update the count.
Can I count colored cells in a filtered list?
Yes, the Find & Select method counts all cells in the selected range, even if filtered. For a dynamic count that respects filters, use the SUBTOTAL function with a helper column that marks colored cells (e.g., using GET.CELL), then use =SUBTOTAL(103, helper_range).
Is there a way to count colored cells without VBA?
Yes, use the Find & Select method for a one-time count. For a dynamic count without VBA, you can use the GET.CELL method with a named range, but that still requires the workbook to be saved as macro-enabled because GET.CELL is an old XLM function.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Disable Autocorrect on iPhone or iPad: Quick Guide
- Microsoft Store Error 0x80073d12: Fixes That Work
- View Notification Count on iPhone: Full Guide
- Custom Keyboard Shortcuts on Windows 11 & macOS
- Restore Windows 11 Display Scaling to Recommended
- Semiconductor Cycle: How Investors Track It0.0