How to count the number of colored cells in Microsoft Excel

By SpreadCheaters
Page last updated: 21/04/2023 | Next review date: 21/04/2025
close

Author bio


SpreadCheaters
View More north_east

Counting the number of colored cells in Excel means determining the total number of cells that have a specific color applied to them. This is useful when you want to quickly analyze data that has been color-coded for a specific purpose, such as highlighting cells that meet certain criteria or indicating the status of data.

In this tutorial, we will learn how to count the number of colored cells in Microsoft Excel. In Excel, multiple methods can be adapted to count the number of colored cells i.e we can use the built-in COUNTBYCELLCOLOUR function, Find function, or the Filter command.

For instance, we have a data set showing sales of a product in different cities. The cities having more than $10,000 in sales are highlighted with green color. We want to count the number of cities with sales above $10,000.

Method 1: Using the COUNTBYCELLCOLOUR Function

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want the COUNTBYCELLCOLOUR function to return the number of colored cells.

Step 2 – Utilize the COUNTBYCELLCOLOR Function

  • Enter the COUNTBYCELLCOLOUR function in the cell.
  • The syntax of the COUNTBYCELLCOLOR function is: 

             COUNTBYCELLCOLOR ( A2:A15,”C1” )

  • The first argument specifies the range containing the colored cells.
  • The second argument is the cell containing the format to be detected in the cells i.e. the color.

Step 3 – Press the Enter Key

  • Press the Enter key.
  • The number of colored cells will be returned.

Method 2: Using the Find Command

Step 1 – Select the Range of Cells

  • Select the range of cells containing the colored cells.

Step 2 – Open the Find Dialog Box

  • Open the Find dialog box by clicking on the Find & Select button in the Home tab and then clicking on the Find option.
  • We can also use the CTRL+F shortcut keys.

Step 3 – Click on the Format List Arrow

  • Click on the Format arrow in the Find tab.

Step 4 – Click on Choose Format From Cell Option

  • Click on the Choose Format From Cell option in the list.

Step 5 – Select Any Cell with the Color

  • Choose a cell that has the color by which you want to count the other cells.

Step 6 – Click on Find All Option

  • Click on the Find All option.
  • The cells having that color will be listed and their count will be displayed in the left bottom of the dialog box.

Method 3: Using the Filter Command

Step 1 – Select the Range of Cells.

  • Select the range of cells containing the colored cells.

Step 2 – Go to the Data Tab and Click on the Filter Button

  • Go to the Data tab.
  • Click on the Filter button in the Sort & Filter section.
  • The filter drop-down arrow will appear next to the header of the selected range.

Step 3 – Click on the Filter Drop-Down Arrow

  • Click on the Filter drop-down arrow.
  • A drop-down menu will appear.

Step 4 – Click on the Filter by Color Option and Select the Color

  • Click on the Filter by Color option in the menu and select the color.
  • The cells with the selected color will be visible only.

Step 5 – Use the SUBTOTAL Function

  • Use the SUBTOTAL function to count the visible cells.
  • The syntax will be:

             SUBTOTAL(102, B4:B16)

  • Where B4:B16 is the range containing the colored cells.