Table of Contents
The ability to quickly and accurately count cells by color in Excel is an invaluable tool for any data analyst. Count Cells by Color in Excel is a powerful feature that allows users to quickly and accurately count the number of cells in a range that contain any given color. This feature can be used to quickly identify trends in data, highlight important data points, and quickly generate accurate totals and averages. For example, it can be used to quickly identify the number of red cells in a range, which could be used to track customer satisfaction ratings, or to quickly identify the average number of green cells in a range, which could be used to track sales performance. Count Cells by Color in Excel is an invaluable tool for any data analyst and can save time and improve accuracy when working with large datasets.
Often you may want to count the number of cells in Excel based on their color.
For example, suppose we have the following dataset and we’d like to count the number of cells by color:
The easiest way to do this is by writing some code in VBA in Excel.
This might seem intimidating if you’re not familiar with VBA but the process is straightforward and the following step-by-step example shows exactly how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Show the Developer Tab in Excel
Next, we need to make sure the Developer tab is visible on the top ribbon in Excel.
To do so, click the File tab, then click Options, then click Customize Ribbon.
Under the section called Main Tabs, check the box next to Developer, then click OK:
Step 3: Create a Macro Using VBA
Next, click the Developer tab along the top ribbon and then click the Visual Basic icon:
Next, click the Insert tab and then click Module from the dropdown menu:
Next, paste the following code into the module code editor:
Function CountByColor(CellRange As Range, CellColor As Range) Dim CellColorValue As Integer Dim RunngingCount As Long CellColorValue = CellColor.Interior.ColorIndex Set i = CellRange For Each i In CellRange If i.Interior.ColorIndex = CellColorValue Then RunningCount = RunningCount + 1 End If Next i CountByColor = RunningCount End Function
The following screenshot shows how to do so:
Next, close the VB Editor.
Step 4: Use the Macro to Count by Color
Lastly, we can use the macro we created to count the number of cells based on color.
First, fill in cells C2:C4 with the colors that you’d like to find the count for.
Then type the following formula into cell D2:
=CountByColor($A$2:$A$11, C2)
Drag and fill this formula down to each remaining cell in column D and the formula will automatically count each of the cells that have specific background colors:
For example, we can see:
- The count of cells with a light green background is 3.
- The count of cells with a light blue background is 4.
- The count of cells with a light orange background is 3.
Note: If you provide a cell color in column C that does not exist in the lookup range, then the function will simply return a value of 0.