How to Count Colored Cells In Excel Without Using VBA – 3 Methods

The Excel file contains information about students from different countries in a university. The Country is color-coded.

The image below shows the count of cells belonging to the same country.

Method 1: Count Coloured Cells Using the Find & Replace Tool in Excel

Step 1:

• Press CTRL+F to open Find and Replace.
• Click Find and Replace >>> Options.

• Click Format.

Step 2:

• Click Choose Format From Cell in Find Format.

• A color picker eye-dropper will be displayed. Move the eye-dropper to C5 and pick the color of the cell.

• If you left-click the cell with the eye-dropper placed on it, you will see the Preview.
• Click Find All.

• Find all the cells filled with the same color.

• You can also find cells filled with other colors, as shown below.

Method 2 – Applying the Table Filter to Count Coloured Cells in Excel

Step 1:

• Select all the cells in the data range with the column headers.
• Press CTRL+T to activate the Table Filter.
• In Create Table, enter the entire data range ($B$4:$C$C14) using an absolute reference.
• Click OK.

Step 2:

• Table Design will be active.
• The column headers of the new table display a small downward arrow at the down-right corner.
• Click the arrow in Country.
• Click Filter by Color.
• A dropdown menu with all the colors used will be displayed. Click yellow (that represents Sweden).

• A new row, named Total, displays the total number of yellow cells.

• You can also find cells filled with other colors. The image below shows the green cells.

Method 3 – Using the Name Manager Feature to Count Colored Cells in Excel

Step 1:

• Click Formulas and select Name Manager.

• In Name Manager, click New.

Step 2:

• Enter COLOREDCELL as the name of the new function.
• Enter the following function in Refers to.
• Click OK.

• Click Close.

Step 3:

• If you start to enter the formula COLOREDCELL in D5, you will see that Excel will suggest the function.
• Click the suggested formula.

• It returns the color code of C5.

• Drag the fill handle to apply the formula to the rest of the cells.

• All color codes for the cells in the Country column are displayed.

Step 4:

• Enter the following formula in G7 to count the total number of cells associated with each color.

• Press ENTER to see the total number of red cells.

• Drag the fill handle to apply the formula to the rest of the cells.

• You will see the total number of colored cells for each color.

Quick Notes

• Though we do not use the VBA macro, the CELL function is based on it. Save the workbook as Excel Macro-Enabled Workbook or in  XLSM format.

Download Practice Workbook

Download the practice book.

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