Excel Count Colored Cells – How to Count Cells by Color in Excel

  • Home
  • / Excel Count Colored Cells – How to Count Cells by Color in Excel

Excel allows you to make changes in data as per your needs. You can change color coding as well to make your workbook appealing. Using styles and themes in Excel makes your data sheet modern. Your data cells get highlighted when you use the colored feature.

In Excel, you can have a wide range of features and not even one can be used for Excel count colored cells. Definitely, you must not want to use third-party tools for this function, that’s why you need to make your own functions. Most probably, you must be familiar with user-defined functions and that’s what we’ll further discuss.

How to Excel Count Colored Cells with Find and Select Command

Using the Find feature in Excel, you can locate the cells on the bases of a certain format. You can find colored cells as well. Once all the same colored cells are located, you can then count them.

count cells select command

  • Open the Home tab and then choose the Find & Select command. Now, click on the Find option.
  • Or else you can even use a keyboard shortcut as well; press CTRL + F to open the Find and Replace menu.

count cells select command2

  • In the Format button, click on the little down arrow and then click on Choose Format From Cell option. As you click on the Format button, it will open up the Find Format menu that lets you choose any combination of formatting.
  • This option is effective when you know which the exact color to find.

count cells select command3

  • Clicking on the little arrow given in the Format menu, you will get options including the format set by selecting a cell.

count cells select command4

  • Click on the Find All option once the format selection is done. On the bottom side of the Find and Replace box, you will see matching all the cells formatted and on the left bottom side, you will notice the count. To select all the cells, press CTRL + A and then click on the Close button, and then all the cell color gets changed.

count cells select command5

In case when returning cells are required in a given column or range only, you can do this by choosing the range and then clicking on the Find All option.

How Excel Count Colored Cells using Filter and SUBTOTAL

To count colored cells in a sheet, you can follow the steps given below:

Using the SUBTOTAL function to count colored cells once filtering is applied.

Let’s understand this method in detail:

count cells filter subtotal

In the given dataset, you can see two background colors, green and orange.

Put the formula in any cell below the dataset.

Formula =SUBTOTAL(102, E1:E20)

count cells filter subtotal2

  • Choose the headers.
  • Click on the Data and choose Sort & Filter option. From this menu, choose Filter and you will see the filter is applied to all the headers.
  • Now, click on a filter drop-down.
  • Open the “Filter by Color” option and choose the color. The given dataset has two colors that’s why the filter shows two colors when filtering these cells.

count cells filter subtotal3

  • Once the filters are applied, the SUBTOTAL function changes and returns the number of cells that appear after filtering.

count cells filter subtotal4

How Excel Count Colored Cells using VBA Code

Do you remember the user-defined function we mentioned earlier in this post?

VBA is considered one of the safest methods when you need to execute Excel-related tasks. For beginners, applying VBA coding is not an easy thing because VBA uses advanced features of Excel.

Let’s get to know how you can apply VBA coding to count colored cells in Excel:

  • First, press ALT + F11 from the keyboard or else you can open the Developer menu. From this menu, click on the “Visual Basic” option, it will open the Visual Basic Editor.

count cells vba

  • Now, from the menu bar, choose Insert and then click on the Module option in the code window.

count cells vba2

Below you can see the code. Just copy and paste it into the code window.

Function Count_Colored_Cells(ColorCells As Range, DataRange As Range)

Dim Data_Range As Range

Dim Cell_Color As Long

Cell_Color = ColorCells.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then

Count_Colored_Cells = Count_Colored_Cells + 1

End If

Next Data_Range

End Function


count cells vba3

Note that you don’t need a Sub Procedure to run VBA, this is a User-Defined Function that’s why to click on the don’t click the Run option once the code is done.

Now, get back to the dataset and define cells with colors. Later on, put the following formula in cell F5.

=Count_Colored_Cells(E5, $B$5:$B$16)

In this formula the arguments are used as:

Count_Colored_Cells: It is the User-Defined function created in the VBA code to count colored cells.

E5: Blue color – defined cell

$B5:$B$16: It is the range of the dataset used with the colored cells.

Press Enter key.

count cells vba4

Now, you will get the count of color-defined cells, for instance, the dataset has 4 Blue cells, that’s why Blue color-defined cell E5 gives the count 4. Simply, drag the cell by Fill Handle to get all the counts of colored cells in the sheet.

count cells vba5

As you can see, the Orange colored cells are 5, the user-defined Count_Colored_Cells function gives us the count 5.

Write your comment Here