How to Count Colored Cells in Excel Sheets

Colored cells in Microsoft Excel can help you organize data, track changes, and make it easier to read. For example, in a customer spreadsheet, you might use a green background to track which customers have made a sale. But how do you count these colored cells?

Method 1: Count Colored Cells With Find and Select

A photo of a laptop with a spreadsheet open on the screen

Excel includes a Find and Select feature that lets you count cells of different colors. Here’s how to use it:

Table of Contents

  1. Select the Home tab then press the magnifying glass icon.
  2. In the drop-down menu, select Find…
Select the magnifying glass icon, then choose Find...
  1. Select the Format… drop-down menu and choose Choose Format From Cell…
Select Format... then Choose Format From Cell...
  1. Use the cursor to select a cell in the color you want to count.
  2. Select Find All. Excel will then display a list of all the cells in your workbook that are of the same color. Below the list, to the bottom-left, there will be a final count of these colored cells.
A screenshot showing the number of colored cells

Method 2: Count Colored Cells Using the Subtotal Function

The subtotal function lets you count the number of formatted cells according to data filters. Here’s how to use the filter function to count colored cells:

  1. Select all of your data by pressing Ctrl A.
  2. Press the Data tab, then select Filter. This will add gray drop-down menus to each of your columns that contain data.
Press Data > Filter
  1. Choose an empty cell and enter the subtotal formula, which is =SUBTOTAL(Function_num, Ref1).
  2. Replace Function_num, with “102.” This tells Excel to count cells with numbers, not including hidden cells. If your cells are words, as in our example, use “103” – this counts cells with any value within them. For Ref1, you need to select the range of data you want Excel to count. So in this case, it would be:

=SUBTOTAL(103, A2:A10)

Type "=SUBTOTAL(103, A2:A10)", replacing each field with the relevant data for your sheet
  1. The subtotal equation should calculate the total number of cells. For this to work, you’ll need to use cells with numbers in them.
  2. Press the Filter drop-down menu and choose Filter by Color. Choose the color you want to filter by.
Select the filter drop-down menu, choose Filter By Color, then select the color
  1. The subtotal will now display the total number of cells in that color.
A screenshot showing the final result of the SUBTOTAL function

Note: If you’re struggling to get the formulas above to work, try learning about function syntax in Microsoft Excel. Once you understand how the syntax works, you’ll be able to troubleshoot any problems you’re having easily.

Counting Colored Cells Made Easy

Hopefully, you can now count your colored cells in Excel easily. While there are other ways to count cells, including complicated formulas and custom VBA functions, they are generally not worth the effort.

About the Author

Leave a Reply

Your email address will not be published. Required fields are marked *

You may also like these