How to Count Colored Cells in Excel (Multiple Methods)

Counting colored cells in Excel is an important part of an Excel’s daily tasks as you deal with data that uses color coding to emphasize certain values.

Being able to count colored cells makes data analysis easy as you categorize data to find a certain value you need. Excel doesn’t offer any built-in function to count cells but there are a couple of hacks we can use to get our way around this task 😃

This tutorial will walk you through all the methods you need to know to count colored cells in Excel. Download our sample workbook here to practice along the guide.

Table of Contents

Use Find option to count colored cells

The simplest and quickest way to count coloured cells in your worksheet is to look for them. It’s similar to looking for a keyword in your worksheet, only in this case you look for the background color of a cell 🎨

Let’s see how do to this below.

Say, we have the following sample data. It consists of names of employees their departments and their status on their daily tasks. Each status info is colored in different colors, we have to count the number of people who have completed their tasks for the day, colored in green, from the data set.

To do that,

Step 1) Click on the Find & Select option under the Editing section on the Home tab or press CTRL + F.

Step 1) In front of the Find what box, click the Format box.

Step 1) A Find Format dialog box will appear on the screen.

Step 2) Select the color you want to count cells in 🟩

Step 3) Press Ok.

Step 4) On the Find and Replace dialog box, press Find All.

It will show you the address of each cell colored in green and will give the total count of cells.

And it’s done! You can do the same for cells in other colors too. The only drawback of this method is that if you want to see the count again, you will have to search again 🔍

Use a table to count colored cells

Another way to count colored cells in Excel is to put your data into a table and then find the count. We will use the same sample data as above.

To count colored cells using table,

Step 1) Apply filter to the column you want to count colored cells in by selecting Sort & Filter from under the Editing section on the Home tab or press CTRL + Shift + L.

Step 2) Small arrow dropdowns will appear next to each column’s name.

Step 3) Select any cell in your data set and press CTRL + T to turn it into a table.

Step 4) Checkmark the My table has headers option if you have any.

Step 5) Press Enter.

Step 6) Once done, open the Table Design tab at the far right of the ribbon 🎀

Step 7) Checkmark the Total Row option under Table style options.

Step 8) In the Total row of the table, click the cell containing the sum.

Step 9) A small box with down arrow will appear, click it and select Count from the dropdown.

Step 10) In the Status column, click on the down arrow.

Step 11) Go to Filter by Color and select green color.

Step 12) This will filter out the green cells in the table and return a count of the visible cells.

How cool is that? Try it now! 😀

Use SUBTOTAL function to count colored cells

You can also use an Excel function to count colored cells in your worksheet. All you need to know is how the SUBTOTAL function works.

Its working is similar to the one in a table but in this case, you need to know the arguments of your formulas too. Let’s learn more about that below 🔽

Say, we have the same sample data as earlier and I want to count the number of red cells.

To do that,

Step 1) Select an empty cell beneath your data set.

Step 2) Type in the following formula:

Step 3) Press Enter.

The function will return the count of values in the given range as:

To extract the count of cells that are red from this data set,

Step 4) Use the Filter feature on column D and select a red color from the options 🚩

Step 5) Press Ok.

Step 6) Excel will now only show the cells that are red in color and the SUBTOTAL function will return the count of these colored cells.

You can do the same for the remaining cells, choosing each from the filter menu.

Pretty easy, no? 😎

Use GET.CELL function to count colored cells

Another way to count colored cells in your worksheet is to use the Macro4 GET.CELL function.

The process of using GET.CELL to count colored cells is very simple and involves three steps 🧐

• Define name range
• Get color code from named range
• Use the color code to count colored cells

Say, we have the following sample data set. Each row is coloured according to the department it holds. We want to find the number of cells in the color brown 🟫

To do that,

Step 1) Go to the Formulas tab.

Step 2) Select Define Name from under the Defined Names section

Step 3) In the New Name dialog box, type in the name of your function – we put GETCOLOR.

Step 4) Leave the scope to the workbook.

Step 5) In the refers to box, insert the following address:

where,

38 is the first argument and refers to the color code

$D2 refers to the cell containing the desired color in Sheet 5

Step 6) Press Ok.

Step 7) Make a helper column next to your data set.

Step 8) In cell F2, type in the GETCOLOR formula as:

Step 9) Press Ok.

Step 10) Double-click the Fill Handle to copy the formula down to the remaining cells ©

The formula will generate a color code for each color in your data set. We want to find the cells colored brown so the cell; value for brown is 44.

Pro tip

If a cell has no background color, it will return 0 as a value.

Now, to calculate the number of times each colour appears,

Step 11) Type in the COUNTIF formula in cell B13 as:

Step 12) Press Enter.

The function returns the count of cells colored brown.

Drag the formula down to the remaining colored cells to get their count.

How cool is that? 😃

Use VBA to count colored cells

In the methods above, we saw how to use count colored cells using different hacks and functions. But what if we could create our function specific to this task? 🤔

We will create a user defined function in this method that will count colored cells using VBA.

Now to make a custom function,

Step 1) Press Alt + F11 on your keyboard – this will open up the Visual Basic editor.

Step 2) From the Insert tab, select Module.

Step 3) Paste the following code into the module.

Step 4) Once done, close the VB editor.

Step 5) Now, use the CountCellsByColor function in your worksheet as:

where,

$E$2:$E$11 refers to the range containing all coloured cells

A13 contains the reference color you are looking for

Step 6) Press Enter.

And it’s done! The function returns the count of brown cells from the given range.

Copy down the formula to find the count for the remaining colors.

Isn’t this the coolest method ever? 🤓

Conclusion

In this tutorial, we saw how to count colored cells in Excel using different ways including using Find feature, VBA, and table. We also saw different Microsoft Excel formulas that we can use to get the count of colored cells in our worksheet and workbook.

You can try all these, methods and then choose the one that suits you best. However, if you are a beginner, the table and find feature might be the best options for you 😉

To learn more about VBA and counting cells in Excel, give the following articles a read:

How to Count Cells With Specific Text Using a Formula

How to Use the COUNT function in Excel (and COUNTA)

How to Count Unique Values in Excel (and Distinct Values)

We hope you enjoyed reading this article as much as we did creating it 🤗