Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Count Colored Cells in Excel: A Comprehensive Guide | MyExcelOnline How to Count Colored Cells in Excel: A Comprehensive Guide | MyExcelOnline

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.

How to Count Colored Cells in Excel: A Comprehensive Guide

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

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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.

How to Count Colored Cells in Excel: A Comprehensive Guide

STEP 2: Click on Insert > Module.

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

 

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.

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

STEP 3: Click on the Options button.

How to Count Colored Cells in Excel: A Comprehensive Guide

STEP 4: Click on the Format button.

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

STEP 6: Select Find All.

How to Count Colored Cells in Excel: A Comprehensive Guide

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

How to Count Colored Cells in Excel: A Comprehensive Guide

 

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!

If you like this Excel tip, please share it
How to Count Colored Cells in Excel: A Comprehensive Guide | MyExcelOnline How to Count Colored Cells in Excel: A Comprehensive Guide | MyExcelOnline
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Add a Leading Zero in Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...