Oftentimes while working with categorical information in Excel, you might need to color all the cells under a specific category in the same color to distinguish one category from the others. But most of the conventional methods will require you to use VBA to do that. But you might find using VBA a bit hard if not being familiar with it. In this tutorial, I will show you how to count colored cells in Excel without VBA.
How to Count Colored Cells In Excel Without VBA: 3 Handy Methods
Let’s assume a scenario where we have an Excel file that contains information about the students from different countries in a university. The worksheet has the Name of the student, Country each of the students from. Along with that, we have color-coded each country in the Country column to visually distinguish them from each other. We will now use this worksheet to learn how to count colored cells in Excel without VBA. The image below shows the Excel worksheet that has the count of cells belonging to the same country.
Method 1: Count Coloured Cells Using the Find & Replace Tool in Excel
One of the ways to count colored cells without VBA is to use the Find and Replace tool. Let’s see how we can do that.
Step 1:
- First, we will press CTRL+F to open the Find and Replace.
- Then, we will click on the Options >>> on the down-right corner of the Find and Replace.
- Now, we will click on the Format.
Step 2:
- After that, we will click on the Format From Cell from the down-left corner of the Find Format.
- Now, a color picker eye-dropper will appear that will let us pick the color of a specific cell. We will move the eye-dropper on cell C5 which has the first colored cell with the country name Canada.
- If we now left-click on the cell while the eye-dropper is placed on it, we will see that the Preview option is filled with the same color of that cell.
- Next, we will click on Find All.
- Finally, we will find all the cells that have been filled with the same color cell C5 has been filled with.
- We can also find out all the cells filled with each of the rest colors. The image below shows that we have found out all the cells filled out with the blue color and the United States as the country.
Read More: How to Count Blank Colored Cells in Excel
Method 2: Apply the Table Filter to Count Coloured Cells in Excel
The easiest way to find out all the colored cells in Excel without VBA is to use the Table Filter. You have to do the following.
Step 1:
- First, select all the cells in the data range along with the column headers. Then, we will press CTRL+T to activate the Table Filter.
- Now, a window titled Create Table will appear. We will insert the entire data range ($B$4:$C$C14) using the absolute reference.
- Next, we will click OK.
Step 2:
- Now, we will see a new tab named Table Design is added along with the existing tab or ribbon.
- The column headers of the newly created table have a small downward arrow on the down-right corner of each of them.
- Next, will click the arrow on the Country.
- Now, a window with a different filter option will appear. We will click on the Filter by Color.
- A dropdown menu with all the colors we have used to fill the cells will appear. We will click on the yellow color that represents the country Sweden.
- Finally, we will see that a new row named Total is showing the total number of cells with the fill color of yellow and Sweden as the country.
- We can also find out all the cells filled with each of the rest colors. The image below shows that we have found out all the cells filled out with the green color and Italy as the country.
Read More: Count Cells by Color with Conditional Formatting in Excel
Method 3: Use the Name Manager Feature to Count Coloured Cells in Excel
The problem with the above two methods is that you have to follow the steps repeatedly to find out the total numbers of cells filled with each color separately. We can not find out all the cell counts for all colors at a time. But there is a solution to this problem. We will create a new function to do that for us. We have to follow the below steps.
Step 1:
- First, we will click on the Formulas tab and select Name Manager under that tab.
- Now, a new window titled Name Manager will appear. We will click on the New button from that window.
Step 2:
- Then, we will write COLOREDCELL as the name of the new function.
- Next, we will write down the following function in the Refers to.
- After inserting the formula, we will then click on OK.
=GET.CELL(38, COLORED!C5)Formula Breakdown:
- GET.CELL is a function based on the VBA macro. But don’t worry!!! we do not need to insert a VBA code to use this function.
- This function will take the first cell(C5) of the column with all the colored cells as an argument. Then it will return the color code of that cell.
- Finally, we will click on the Close button to close the Name Manager.
Step 3:
- Now, if we start to write the formula COLOREDCELL in cell D5, we will see that Excel will suggest the function to use.
- So we will click on the formula from that suggestion list.
- Now, the function will return the color code of cell C5.
- Then, we will drag the fill handle to apply the formula to the rest of the cells.
- Finally, we will get all the color codes for the cells in the Country
Step 4:
- Now, we will write down the following formula in cell G7 to count the total number of cells associated with each color.
=COUNTIF($D$5:$D$14,COLOREDCELL)Formula Breakdown:
- COUNTIF function will take $D$5:$D$14 and the function COLOREDCELL as arguments. It will find out the count of cells associated with each color.
- Upon pressing ENTER, the function will now return the total number of cells filled with red.
- Then, we will drag the fill handle to apply the formula to the rest of the cells.
- Finally, we will see that the total number of colored cells for each color is shown beside the respective country.
Read More: Excel Formula to Count Cells with Specific Color
Quick Notes
- Though we do not use the VBA macro, the CELL function is based on it. So, we have to save the workbook as Excel Macro-Enabled Workbook or in the XLSM format.
- Also if you want to use VBA macro to count colored cells in excel, you can read this article.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
In this article, we have learned how to count colored cells in Excel without VBA. I hope from now on you can count colored cells in Excel without VBA very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
