Excel: Count Cells that are colored

Coloring is the act or process of applying color to certain surfaces, objects, items, or equipment. Coloring grants an item or an object a distinctive appearance from the other items or objects. Mostly coloring is done to improve on the appearance and to make the item or the object clearer and visible.

Coloring is not only done to surfaces, items, or objects but also coloring is done to cells in an excel sheet. Excel sheets are colored to differentiate the data they are holding from the data held by the other cells.

In cases where the excel sheets are colored, the tally or the count of the colored cells is taken or it is needed. To count the colored cells is the act or process of counting or tallying the colored cells in numeral values. Cells are counted to come up with a numeric value of all the cells in any given excel sheet.

To come up with a correct tally of colored cells in an excel sheet, here are some of the steps or procedures.

Steps to count colored cells in Excel

1. Assuming we have an excel sheet of the sort as shown below in the image.

2. To get the correct count of all the colored cells, there are some of the methods but we are going to have a look at one called the filter and SUBTOTAL method. In this method, we are going to use the subtotal formula which is =SUBTOTAL (102, B1: B8). Write the formula on the result cell E9 and click on enter.

You will realize that it gets the counts of all cells even the uncolored ones. To change this, follow the following step.

3. Select the headers, click on the data button from the menu bar, and then click on filter.

4. Click on the dropdown arrow in any column and Select filter by color,

5. The cells are colored in orange, the only color that will appear will be orange, click on it. The cells that were not originally colored in orange will be hidden and the cells in orange remain. Now try getting the count again using the formula used in step 2.

And 4 is the correct count of the colored cells in this excel sheet.

NOTE: To calculate the total number of colored cells in the above data you need to change the range in the formula to A2:B7

How to count cells that are not colored (No fill)

1. Let's use the following data for illustration purpose

2. Click on an empty cell and enter the following formula on the formula bar =SUBTOTAL(102, B1:B8). It will count the total number of cells in column B

3. Now that we know the total number of cells, it's time to count only the non-colored cells. Select the top row and click on Sort and Filter >Filter. Your columns will have drop-down arrows like shown below

4. Click on any arrow and select Filter by Color>No Fill

5. Repeat the formula in step 2 to count the cells