Dear Analyst #115: How to count the number of colored cells or formatted cells in Google Sheets

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.

Video walkthrough:

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:

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:

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:

Let’s say I filter this column by the yellow background color. You’ll see this results in a filtered data set with 9 rows remaining:

In order to actually count the number of cells in this filtered data set, you might be tempted to do a COUNTA() formula, but let’s see what happens when I put this into cell B51:

The formula counts all the rows in the data set including the rows that have been filtered out. Instead, you can use the SUBTOTAL() formula which magically returns the sum, count, etc. for a filtered data set. The key is to use the value “3” for the first parameter to tell Google Sheets to count only the cells in the filtered data set:

I don’t think this is the usual use case for the SUBTOTAL formula. But like many formulas in Google Sheets/Excel, it works! To recap on this method:

Pros

  • Easy to use and implement
  • Doesn’t require the use of VBA or Google Apps Script
  • Since it’s a formula, it’s dynamic and can change as your data changes (with caveats)

Cons

  • Requires a few steps to get it to work (e.g. filter your data set by a color)
  • Each time you want to count the number of formatted cells, you need to re-filter by a different color
  • Since your data is filtered, you can’t easily update the source data and requires you to re-filter by a color

Method #2: Filtered views to allow for dynamic updating of data with the SUBTOTAL formula

This is an extension of method #1. One of the cons of method #1 is that once you’ve filtered your data set, you need to un-filter the data set if you want to add or remove formatting from your cells. For instance, in column B we have a bunch of yellow colored cells. If you want to highlight another cell as yellow and then re-count the number of cells that are colored yellow, you have to un-filter the data set, highlight the cell that needs to be colored yellow, re-filter the column, and re-write the SUBTOTAL formula (assuming you put it at the bottom of column B):

To avoid filtering and un-filtering the data set, you can create a filtered view of the data set. Additionally, you can put the SUBTOTAL formula somewhere that’s not at the bottom of the data set. Let’s first create a a filtered view just on the background color yellow and we’ll call it “Yellow Cells”:

Now you can quickly switch between the filtered view of yellow-colored cells and the unfiltered data set:

Then we can put the SUBTOTAL formula somewhere below the bottom of the data set. Notice now how when we switch between the filtered view and the unfiltered data set, the SUBTOTAL formula automatically updates:

While this method is an improvement on method #1, it still has some drawbacks. A recap of this method:

Pros

  • Easily switch between the filtered and unfiltered data set
  • Update cells with new colors and have that flow into the SUBTOTAL formula dynamically

Cons

  • Filtered views are not an easily discoverable feature in Google Sheets
  • Still requires you to go through the Data menu and flip back and forth when you want to count the number of colored cells

Method #3: A macro to count the number of colored or formatted cells in a range

Almost all the other solutions for counting the number of colored or formatted cells on the Internet refer to a VBA script for Excel. This is a macro for Google Sheets using Google Apps Script. You can copy and paste the script from this gist. When you run the CountFormattedCells macro in Google Sheets, it counts all the cells that have a background color in column B below. It then outputs the count of cells in cell 52 after you’ve selected a range of cells where you want to count the colored cells:

If you want to specify a color to count, you can color cell C53 with color you want to count. Let’s say I want to count only the green cells. I would color cell C53 with green, select all the cells where I want to find the color green, and then run the macro:

The key to making this work is setting some variables up in the script. The two variables you have to set in the script are outputNumberOfFormattedCells and cellWithFormatToCount. The cells you pick will depend on the specific spreadsheets you’re working with. In the script below, you’ll see that you have to edit the first two variables fit the needs of your Google Sheet:


function CountFormattedCells() {
  
  // Output the number of formatted cells somewhere in your spreadsheet
  var outputNumberOfFormattedCells = 'C52'

  // Cell that contains the color you want to count. Default is blank.
  var cellWithFormatToCount = 'C53'

  var spreadsheet = SpreadsheetApp.getActive();
  var currentRangeColors = spreadsheet.getActiveRange().getBackgrounds();
  if (cellWithFormatToCount !== '') { var cellWithFormat = spreadsheet.getRange(cellWithFormatToCount).getBackground(); }
  var formattedCellCount = 0
  for (var i in currentRangeColors) {
    for (var j in currentRangeColors[i]) {
      if (currentRangeColors[i][j] !== '#ffffff' && cellWithFormatToCount == '') {
        formattedCellCount++
      } else if (cellWithFormatToCount !== '' && currentRangeColors[i][j] == cellWithFormat) {
        formattedCellCount++
      }
    }
  }
  if (outputNumberOfFormattedCells != '') {
    spreadsheet.getRange(outputNumberOfFormattedCells).setValue(formattedCellCount)
  }
};

The macro is very easy to use but it does require you knowing how to add macros to your Google Sheet and editing the script in Google Apps Script. The recap for this method:

Pros

  • Script is easy to copy and paste into Google Apps Script and works right out of the box
  • Just two variables to customize
  • Doesn’t require any filtering of your data set or any formulas
  • Can assign a keyboard shortcut to the macro to quickly run the macro
  • Could assign a time-based trigger to the macro so that it runs every minute or hour to give you a “dynamic” count

Cons

  • Requires knowledge of macros and editing a Google Apps Script
  • May need to change the location of the cell where you output the count of colored cells if your data changes a lot over time
  • Requires running the macro each time you want to get an updated count of the colored cells

Bottom line

None of these methods are that simple or easy to use in my opinion. Usually I have a preferred method for solving some Google Sheets or Excel problem, but in this case I can’t say I like or dislike a method over another one. If I had to pick one, I’d use method #3 since I’m comfortable with macros and editing Google Apps Scripts. But the Google Apps Script solution is far from easy to use for a beginner to Google Sheets.

The SUBTOTAL formula is indeed much easier to implement, but also comes with the added inconvenience of constantly filtering and unfiltering your data set.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting: