How to Count Colored Cells in Excel (4 Simple Ways)

In this dataset, there are three categories: Fruit, Flower, and Food, each marked with a different color. Let’s count the cells with a specific color, as shown in the GIF.

An Overview of How to Count Colored Cells in Excel


Method 1 – Using the Find & Select Command

  • Select the data range with colored cells.
  • Go to the Home tab, click on the Find & Select drop-down, and choose Find.Selecting Find Command to Count Colored Cells in Excel
  • A Find and Replace dialog box will pop up.
  • Click Options.
  • Click on the Format drop-down and go to Choose Format From Cell.
  • Choosing Format From Cell to Count Colored Cells in ExcelA four-dimensional plus symbol will appear.
  • Place the plus symbol over any colored cell and click on it. We picked the color Blue.
    Choosing Color of the Cell to Find
  • The Preview label box will be filled with a color of the cell that you picked earlier.
  • Select Find All.
    Clicking on Find All Option Find the Previewed Cells
  • You will get all the details of the specified colored cells, along with the count of those colored cells.Finding All the Particular Colored Cells to Count Colored Cells in Excel

Method 2 – Using the SUBTOTAL Function and the Filter Tool

  • Select a blank cell below the data range.
  • Apply the formula: =SUBTOTAL(102,B5:B16)

Here, the first argument set to 102 counts only the visible cells (hidden rows are excluded) in the given range. You will get the total count of the cells in the range.
Applying SUBTOTAL Function to Count Colored Cells in Excel

  • Select only the headers of the data range.
  • Go to the Data tab and select Filter.
    Implementing Filter Command to Count Colored Cells in Excel
  • This will insert a drop-down button in each header of the dataset.
  • Click the drop-down button in the header of the column with colored cells.
  • Choose “Filter by Color” from the drop-down list to see all colors from your data range in a sub-list.
  • Click on the color you want to count.
    Filtering by Color
  • Excel will display only cells with the chosen color and show the count in the SUBTOTAL result cell.

Applying Filter to Count Colored Cells in Excel

  • You can count all the other colored cells in your worksheet in Excel.

Applying Filter to Another Colored Cells


Method 3 – Applying GET.CELL Macro 4 and COUNTIFS Function

Step 1 – Create a Name Range

  • Go to Formulas tab and the Define Names group, then select Define Name.
    Selecting Define Name to Count Colored Cells in Excel
  • In the New Name pop-up box, use the following:
    • Name: GetColorCode (this is a user-defined name)
    • Scope: Workbook
    • Refers to: =GET.CELL(38,GetCell!$B5)
      Here, 38 means the color of the referenced cell. GetCell means the sheet name that has your dataset. $B5 is the reference of the column with the background color.
    • Click OK.

    Defining Name and Formula

Step 2 – Find the Color Code for Each Cell

  • In a cell adjacent to the data, write the user-defined formula:=GetColorCode
  • Press Enter.
    Getting Color Codes
  • The formula will return a specific number specified in color.
  • Drag the cell down with the Fill Handle.
    Auto Filling Formula
  • All the cells with the same background color will get the same number, and if there is no background color, the formula will return 0.

Step 3 – Apply the COUNTIFS Function

  • Select the cell where you want to see the count of colored cells.
  • Apply the formula:=COUNTIFS($E5:$E$16,GetColorCode)
    Here, $E5:$E$16 is the range of the color code that we extracted from the user-defined formula.Inserting COUNTIF Formula
  • You will get the count of the color-defined cells.
  • Click on the next cell.
  • Enter the following formula: =COUNTIFS($E5:$E$16,GetColorCode)Utilizing Get.Cell to Count Colored Cells in Excel

Method 4 – Using Excel VBA Code

  • Go to the Developer tab and select Visual Basic to open the Visual Basic Editor. Alternatively, press Alt + F11.Opening Visual Basic Window
  • Navigate to the Insert tab and select Module to create a new module for writing your code.
    Inserting Module
  • Copy and paste the following code:
    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

    Writing CodeThis is not a Sub Procedure for the VBA program to run; this is creating a User Defined Function (UDF). So, after writing the code, don’t click the Run button from the menu bar.

  • Go back to the dataset and define cells with colors as we did in the previous method.
  • Use the following formula:=Count_Colored_Cells(E5,$B$5:$B$16)
    Here, Count_Colored_Cells is the user-defined function that you created in the VBA code. E5 is the color-defined cell and $B5:$B$16 is the range of the dataset with colored cells.
  • Press Enter.
    Inserting UDF to Count Colored Cells in ExcelYou can see the count of colored cells.
  • Drag down the cell using the Fill Handle tool.
    Implementing VBA Code

Download the Practice Worksheet

Frequently Asked Questions

Is Excel VBA coding necessary to count colored cells?

No, VBA coding is not necessary, but it offers advanced customization options. Users with basic Excel skills can effectively count colored cells using simpler methods like formulas and filters.

Can I count colored cells in a specific range?

Yes, you can count colored cells in a specific range by adjusting the range parameter in your counting formula or method. This allows you to focus on a particular subset of your data.

Are there any limitations to counting colored cells in Excel?

While Excel provides various methods to count colored cells, these methods may have some limitations, such as compatibility issues or the need for adjustments when dealing with large datasets. It’s essential to choose a method that suits your specific requirements and constraints.

Count Colored Cells in Excel: Knowledge Hub


<< Go Back to Count Cells | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

6 Comments
  1. The VBA work for me, but it does not automatically update the count when I change the color, it only updates the count when I redrag the formula back and forth. Thank you in any case, and perhaps we’ll be able to find new ways to improve this with automatic updates.

    • We’re glad to know that we could help you out. In the case of the VBA method, kindly refresh the worksheet after you change the color of the cells. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

      Good luck.

  2. Yeah, managed to get this to work well but again, it does not auto update unless you update the cells.

    • We’re happy to help you out. Kindly refresh the worksheet if you change the cell colors. You’ll find the Refresh button in the Data tab. However, if we can improve the code to update it automatically, we’ll let you know.

      Good luck.

  3. Your steps are not numbered. Step 6 of the macro approach is not defined.

    • Thank you very much for correcting us. We removed the reference as it’s not really necessary. You just have to color the cells E5 and E6 in blue and orange respectively. This is for the purpose of taking reference in the argument of the function we inserted in cells F5 and F6.

      Thank you again.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo