Function Problems

How to Count Cells by Color in Excel: Simple No-Code Methods

Steve K Oct 9, 2026 872 views

Question details

The user needs a simple, non-technical method to count cells based on their fill color, as well as a way to identify the color value of specific cells.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing and analyzing worksheet data that has been visually categorized using different cell background fill colors.
Observed behavior: Excel does not have a standard built-in formula (like COUNTIF) that can directly evaluate or count cells based on their formatting or fill color.

Before you start

Ensure your data is organized in a clear table structure with a header row, as the filtering method requires column headers to work properly.

Solution 1Recommended

Use the Filter Feature combined with the SUBTOTAL Function

Since Excel doesn't have a built-in function to count by color, combining the AutoFilter tool with the SUBTOTAL function is the most reliable, no-code workaround.

The SUBTOTAL function is unique because it can be set to ignore hidden rows. By filtering your data to show only a specific color, SUBTOTAL will calculate only the visible cells.

Prepare the SUBTOTAL formula

Select a blank cell below or outside your data table. Type the formula =SUBTOTAL(103, A2:A100) and press Enter. Replace 'A2:A100' with the actual range of your data. The number 103 instructs Excel to count all non-empty visible cells.

Enable the AutoFilter tool

Select the header row of your dataset. Navigate to the 'Data' tab on the Excel ribbon and click the 'Filter' button. This will add drop-down arrows to your column headers.

Filter the column by color

Click the drop-down arrow on the header of the column containing your colored cells. In the menu that appears, hover over 'Filter by Color'.

Select the color and view the count

Click on the specific fill color you want to count. Excel will hide all rows that do not contain this color. Check your SUBTOTAL formula cell; it will automatically update to show the exact count of the visible colored cells.

Use the Filter Feature combined with the SUBTOTAL Function

SUBTOTAL Function Codes: Using 102 in the SUBTOTAL formula counts only numbers in visible cells, while 103 counts any visible non-empty cells (similar to COUNTA), making 103 the best choice for mixed data.

Solution 2

Use the Find and Replace Dialog Box

The Find and Replace tool can quickly locate and count cells of a specific color across your entire worksheet without writing any formulas.

Efficient Data Management

Count and Manage Colored Cells Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data filtering and calculation functions, allowing you to easily manage colored cells and analyze your data just like Microsoft Excel, but completely free.

1. Open your file: Launch WPS Office and open your spreadsheet document.
2. Insert the SUBTOTAL formula: In an empty cell below your data, enter =SUBTOTAL(103, [Your Data Range]) to count visible non-empty cells.
3. Apply AutoFilter: Select your column headers, navigate to the 'Data' tab on the top ribbon, and click 'AutoFilter'.
4. Filter by Color: Click the filter drop-down on your target column, hover over 'Filter by Color', and choose the desired color to instantly update your count.

Fully compatible with Microsoft Excel (.xlsx, .xls) formatsSupports advanced filtering by color and SUBTOTAL calculationsClean, familiar interface with zero learning curveLightweight application that runs smoothly on Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Is there a built-in Excel formula to count colored cells directly?

No, standard Excel does not have a dedicated function like =COUNTBYCOLOR(). You must use workarounds such as Filter + SUBTOTAL, Find and Replace, or write a custom VBA macro (User Defined Function).

How do I identify the exact color value (RGB/HEX) of a cell?

To find a cell's exact color value, select the cell, right-click, and choose 'Format Cells'. Go to the 'Fill' tab, click 'More Colors', and look under the 'Custom' tab. Here you will see the exact Red, Green, and Blue (RGB) values used for that fill.

Why doesn't the COUNTIF function work for colored cells?

The COUNTIF and COUNTIFS functions are strictly designed to evaluate the text, numbers, or date values stored inside a cell. They cannot read or evaluate cell formatting, such as background fill color or font color.

Does the SUBTOTAL count automatically update if I change a cell's color?

No. Changing a cell's formatting does not automatically trigger Excel to recalculate formulas or re-apply active filters. If you change a cell's color, you must re-apply the filter (Data tab > Reapply) to see the updated SUBTOTAL count.

Excel & Data Problems