Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Colored Cells In Excel Without VBA (3 Methods)

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.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Methods to Count Colored Cells In Excel Without VBA

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.

How to Count Colored Cells In Excel Without VBA


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.

Count Coloured Cells Using the Find & Replace Tool in Excel

  • Now, we will click on the Format.

Count Coloured Cells Using the Find & Replace Tool in Excel

Step 2:

  • After that, we will click on the Format From Cell from the down-left corner of the Find Format.

Count Coloured Cells Using the Find & Replace Tool in Excel

  • 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.

Count Coloured Cells Using the Find & Replace Tool in Excel

  • 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.

Count Coloured Cells Using the Find & Replace Tool in Excel

  • Finally, we will find all the cells that have been filled with the same color cell C5 has been filled with.

Count Coloured Cells Using the Find & Replace Tool in Excel

  • 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.

Count Coloured Cells Using the Find & Replace Tool in Excel

Read More: How to Count Blank Colored Cells in Excel (2 Methods)


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.

Apply the Table Filter to Count Coloured Cells in Excel

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.

Apply the Table Filter to Count Coloured Cells in Excel

  • 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.

total number of cells

  • 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.

Apply the Table Filter to Count Coloured Cells in Excel

Read More: Count Cells by Color with Conditional Formatting in Excel (3 Methods)


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.

Name Manager from Formulas Tab

  • Now, a new window titled Name Manager will appear. We will click on the New button from that window.

Use the Name Manager Feature to Count Coloured Cells in Excel

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.

GET.CELL Formula

  • Finally, we will click on the Close button to close the Name Manager.

Use the Name Manager Feature to Count Coloured Cells in Excel

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.

Use the Name Manager Feature to Count Coloured Cells in Excel

  • Now, the function will return the color code of cell C5.

color code of cell C5

  • Then, we will drag the fill handle to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will get all the color codes for the cells in the Country

Use the Name Manager Feature to Count Coloured Cells in Excel

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.

Use the Name Manager Feature to Count Coloured Cells in Excel

  • Upon pressing ENTER, the function will now return the total number of cells filled with red.

Use the Name Manager Feature to Count Coloured Cells in Excel

  • Then, we will drag the fill handle to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will see that the total number of colored cells for each color is shown beside the respective country.

total number of cells

Read More: Excel Formula to Count Cells with Specific Color (4 Ways)


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.

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!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo