Counting the number of colored cells or formatted cells in Google Sheets or Excel seems like it should be a basic operation. Unfortunately after much Googling, it doesn’t seem as easy as it looks. I came across this Mr. Excel forum thread where someone asks how to count the number of rows where there is a colored cell. The answers range from VBA to writing formulas that indicate whether a cell should be colored to the usual online snark. I think the basic issue is this. A majority of Excel or or Google Sheets users will have a list of data and they will color-code cells to make it easier to read or comprehend the data. No fancy formulas or PivotTables. Just coloring and formatting cells so that important ones stick out. I thought this would be a simple exercise but after reading the thread, I came up with two solutions that work but have drawbacks. The Google Sheet for this episode is here.

<figure class="wp-block-image size-large"></figure>

Video walkthrough:

<figure class="wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio"></figure>

Color coding HR data

In the Mr. Excel thread, the original poster talks about their HR data set and the rules their team uses to color-code their data set. Many people in the thread talk about setting up rules for conditional formatting (which I agree with). But it sounds like people just look through the data set and manually color code the cells based on the “Color Key” mentioned in the post:

<figure class="wp-block-image size-full"></figure>

I think this manual color coding of cells is very common. Yes, someone could write conditional formatting logic to automate the formatting and color coding of these cells. But for most people, I’d argue just eyeballing the dataset and quickly switching the background or foreground color of the cell is easier, faster, and more understandable for a beginner spreadsheet user. If there isn’t that much data, then manually color coding cells feels less onerous.

I put a subset of the data into this Google Sheet and manually color-coded some of the cells into column B below:

<figure class="wp-block-image size-large"></figure>

Method #1 for counting colored cells: Filter by color and the SUBTOTAL formula

The quickest way to count the number of cells that have a certain color format is to filter the column by color. After applying the filter to all the column headers, you can filter a column by the cell’s background color through the column header menu. Filter by color -> Fill color -> Desired color:

<figure class="wp-block-image size-large"><img decoding="async" width="1024" height="498" data-attachment-id="53188" data-permalink="https://www.thekeycuts.com/dear-analyst-115-how-to-count-the-number-of-colored-cells-or-formatted-cells-in-google-sheets/3-filter-column-by-color-google-sheets/" data-orig-file="https://i0.wp.com/www.thekeycuts