In this article, we will show you how to count colored cells in Excel. Using colors in the workbook is a great way to make it look more attractive. But, as there isn’t any built-in function to count the colored cells in Excel, people usually avoid coloring cells. But it can be done with some tricks. Here, we will count the number of the cell with a particular color like the following image.
Download Practice Template
You can download the free practice Excel template from here and practice on your own.
4 Easy Ways to Count Colored Cells in Excel
In this section, you will learn how to count colored cells in Excel by utilizing Excel command tools and User-Defined Functions (UDF). Consider the following dataset, where there are three categories of data, Category: Fruit, Flower and Food. And every category is differentiated by different colors. Fruit category declared in color Blue, category Flower in Orange and category Food has no background colors.
1. Using Find & Select Command
The Find & Select command is one of the most useful tools in Excel to execute any Excel related tasks. Here, we will utilize it to count colored cells in Excel. Now we will learn how to find out the count of each color that each cell of each category holds.
- Firstly, select the dataset with colored cells.
- Secondly, go to the Editing tab from the Home tab.
- Finally, in the Editing tab, select Find & Select -> Find.
- After that, from the pop-up Find and Replace box, click Options.
- Then, click on the drop-down list in Format -> Choose Format From Cell.
- A four-dimensional plus symbol will appear. Place that symbol over any colored cell and click on it (we picked the color Blue).
- Again, the pop-up Find and Replace box will appear, and you will notice, that the Preview* label box will be filled with a color similar to the color of the cell that you picked earlier.
- Finally, click Find All.
- As a result, you will get all the details of the specified colored cells along with the count of that colored cells.
In the same way, you can count all the other colored cells in your worksheet in Excel.
2. Applying Filter Command and the SUBTOTAL Function
Using Excel’s Filter tool and inserting a SUBTOTAL function in it, is another efficient way to count colored cells in Excel. And we can utilize that to count the colored cells in Excel as well. Now we will learn the steps to find out the count of those colored cells in Excel using Filters and the SUBTOTAL function.
- To begin with, click on the C12 cell in the worksheet, write the following SUBTOTAL formula,
102 = The count of the visible cells in the specified range.
B5:B16 = The range of the colored cells.
- You will get the total count of the colored cells in the sheet (e.g. we have 12 cells with background colors, so the SUBTOTAL gave us an output of 12).
- Next, select only the headers of the dataset.
- Thereafter, go to Data -> Filter.
- Consequently, it will insert a drop-down button in each header of the dataset.
- After that, click on the drop-down button from the header of the column that has colored cells in it (e.g. Product Name).
- From the drop-down list, select Filter by Color and you will get all the colors from your dataset in a sub-list.
- Finally, click on the color that you want to count (e.g. we picked the color Blue).
- As a result, it will show you only the cells colored with that specified color along with the counts of those cells in the SUBTOTAL result cell (e.g. there are 4 Blue colored cells in our dataset).
- In the same way, you can count all the other colored cells in your worksheet in Excel (e.g. when we picked the color Orange from the drop-down list, it gave us the cells colored with Orange and as we have 5 cells colored with Orange in our dataset so the SUBTOTAL result cell produced 5)
Read More: Count Cells by Color with Conditional Formatting in Excel (3 Methods)
3. Utilizing GET.CELL 4 Macro and COUNTIFS Functions
The use of Excel 4.0 Macro functions is limited due to its compatibility and difficulty reasons. Another reason is, that this is an old macro function in Excel, so some new features are missing. But if you are still comfortable with working with the EXCEL 4.0 Macros, then we will help you to utilize the function of counting the colored cells in Excel. With the same dataset that we have been practicing on, we will learn how to implement a Macro 4 function to count colored cells in Excel.
- First, go to Formulas -> Define Names >> Define Name.
- Next, in the New Name pop-up box, write the following,
- Name: GetColorCode (this is a user-defined name)
- Scope: Workbook
- Refers to: =GET.CELL(38,GetCell!$B5)
GetCell = Sheet name that has your dataset
$B5 = Reference of the column with the background color.
- Click OK
Now you have a user-defined formula, =GetColorCode.
- Thereafter, in the adjacent to the data, write the formula and press Enter.
- Consequently, it will produce a number (e.g. 42).
- Now, drag the cell down by Fill Handle to apply the same formula to the rest of the cells.
The formula will return specific numbers specified to colors. So all the cells with the same background color will get the same number, and if there is no background color, the formula would return 0. We created a table named Color Count, and in that table, we defined the Cells G5 and G6 according to our color Blue and Orange respectively, and keep the cells next to these (Cells H5 & H6) empty, so that we can get our colored cells count in those cells.
- After that, write the following formula in the H5 cell where you will be having the count of the colored cell,
$E5:$E$16 = the range of the color code that we extracted from the user-defined formula.
- Then, press Enter.
- As a result, you will get the count of the color-defined cells (e.g. there are 4 Blue colored cells in our dataset, so next to the Blue color-defined cell (G5), it gives us the count 4).
- After that, click on the H6 cell and enter the following formula,
As we have 5 cells colored with Orange in our dataset, the user-defined GetColorCode formula gave us the count 5.
Read More: How to Count Blank Colored Cells in Excel (2 Methods)
4. Implementing VBA Code (a User-Defined Function)
The implementation of the VBA code in Excel-related tasks is the safest and most effective method, thus it requires advanced-level skills from the users. And remember about the new features that we mentioned in the previous Macro 4 section, well, VBA is the advancement of Excel 4.0 macro. Let’s get you started with the implementation of the VBA code to count the colored cells in Excel.
- Firstly, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Secondly, in the pop-up code window, from the menu bar, click Insert -> Module.
- Copy the following code 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
This 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.
- Now, go back to the dataset and define cells with colors as we did in the previous method.
- After that, in the F5 cell, write the following formula,
Count_Colored_Cells = the user-defined function that you created in the VBA code (Count_Colored_Cells, in the first line of the code).
E5 = Blue color-defined cell
$B5:$B$16 = the range of the dataset with colored cells.
- Press Enter.
You will get the count of the color-defined cells (e.g. there are 4 Blue colored cells in our dataset, so next to the Blue color defined cell (E5), it gives us the count 4).
- Next, drag the cell through the whole column by Fill Handle to get all the counts of your colored cells in the worksheet.
As we have 5 cells colored with Orange in our dataset, the user-defined Count_Colored_Cells function gave us the count 5.
Read More: How to Count Colored Cells in Excel with VBA (4 Easy Methods)
Count Colored Cells in Excel: Knowledge Hub
- Count Colored Cells
- Count Cells with Specific Color
- Count Blank Colored Cells
- Count Cells by Color with Conditional Formatting
- Count Colored Cells in a Row
- Count Colored Cells Without VBA
- Count Colored Cells with VBA
This article showed you how to easily count colored cells in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
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.
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.
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.