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

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

1. Select the Home tab then press the magnifying glass icon.
2. In the drop-down menu, select Find…

3. Select the Format… drop-down menu and choose Choose Format From Cell…

4. Use the cursor to select a cell in the color you want to count.
5. 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.

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.

3. Choose an empty cell and enter the subtotal formula, which is =SUBTOTAL(Function_num, Ref1).
4. 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)

5. The subtotal equation should calculate the total number of cells. For this to work, you’ll need to use cells with numbers in them.
6. Press the Filter drop-down menu and choose Filter by Color. Choose the color you want to filter by.

7. The subtotal will now display the total number of cells in that color.

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.