How to Count Colored Cells in Excel: A Comprehensive Guide

Microsoft Excel is a powerful tool for data analysis and manipulation, but sometimes the default functions may not be enough to fulfill a user’s requirement. One such scenario occurs when you want to count colored cells in Excel. Excel doesn’t offer a built-in function for this purpose, there are several methods and techniques you can use to count colored cells effectively –

Download the Excel Workbook below to follow along and understand How to Count Colored Cells in Excel –

download excel workbookCount-Colored-Cell-in-Excel.xlsm

 

Method 1 – Filter Option

Suppose you have sales data in a table with a few rows highlighted in red color and you want to count colored cells in Excel.

Follow the steps below to count colored cells using the filter option.

STEP 1: Click on the Data tab and select the Filter button.

STEP 2: Click on the filter button and then select Filter by color > Red color.

STEP 3: Observe the bottom left of the screen to view the count of colored cells.

This straightforward approach allows for quick visualization and counting of colored cells.

 

Method 2 – VBA Code

VBA (Visual Basic for Applications) is a very valuable and powerful programming language in Excel. The VBA loop enables you to execute a custom task based on the instructions provided by you. Follow the steps below to know more –

STEP 1: Open the Workbook and go to Developer > Visual Basic to open the VBA editor.

STEP 2: Click on Insert > Module.

STEP 3: Write the following code –

Function CountCellsByColor(TargetColorCell As Range, DataCells As Range) As LongDim DataCell As Range
Dim TargetColor As Long
TargetColor = TargetColorCell.Font.ColorIndex
For Each DataCell In DataCells
If DataCell.Font.ColorIndex = TargetColor Then
CountCellsByColor = CountCellsByColor + 1
End If
Next DataCell
End Function

STEP 4: In the worksheet, type the custom function =CountCellsByColor.

STEP 5: Enter the 1st argument i.e. the cell that contains the sample font color. Here, it is cell E1.

STEP 6: Enter the 2nd argument i.e. the range in which you need to count. Here, it is A2:A890.

This custom function will count colored cells in Excel and provide you with the result. Here, the result is 21.

 

Method 3 – FIND option

You can also use the FIND option to get the count of colored cells in Excel. Let’s see how –

STEP 1: Highlight the range where you want to find colored cells.

STEP 2: Press Ctrl +  F to open the Find and Replace dialog box.

STEP 3: Click on the Options button.

STEP 4: Click on the Format button.

STEP 5: Select Font and then select a red color.

STEP 6: Select Find All.

So, a total of 21 cells were found with the font color red.

 

Conclusion

This comprehensive guide demonstrates diverse methods to count colored cells in Excel, catering to various user preferences and requirements.

The filter option is like a quick and easy method to count, while VBA code is a more advanced way that gives you more control. The FIND option is another tool that adds flexibility, letting you find colored cells in a unique way.

Whether utilizing simple filtering, diving into VBA programming, or leveraging FIND options, users can confidently navigate the colorful landscape of Excel data analysis.

Click here to learn more about how to count colored cells in Excel!