How to count colored cells in Excel
Excel spreadsheets are used for all sorts of things. Regardless of what you use a spreadsheet for, chances are you color cells in to make the information easier to understand. Colored cells are also added when you apply conditional formatting but you’re free to use any color for a cell to highlight values that have something in common.
Colored cells in Excel
A colored cell in Excel doesn’t behave differently than cells with no fill color. That said, they do look different and if you’d like to count how many times a particular colored cell appears in a sheet, you’ll find there is no straight forward way to do it. You can use data filters to count colored cells in Excel.
Count colored cells in Excel
This method allows you to count cells of a particular color in a given column. If you have the same color repeating in different columns, you will need to count the cells for each column separately and then add them together.
- Open the Excel file with colored cells.
- Select a column by clicking the column head.
- Once selected, go to the Data tab on the ribbon.
- Click the Filter button.
- Open the dropdown at the top of the selected column.
- Select Filter by Color.
- Select the color you want to count cells for.
- Once the filter is applied all the cells with the selected color will move to the top.
- Select all the cells with the same color.
- Look at the bottom right to see the number of selected cells.
- Remove the filter to restore the cells to their original order
Note: If the cell itself if empty and only has color filled in, Excel will not count it.
Limitations
This method has its limitations. For one, if a cell is empty, it won’t be counted. You will have to add some sort of null value e.g. a zero to the cell if you want to count it.
When a filter is applied, it rearranges the cells so that your preferred cells are at the top. This means that you data will look different when you’re counting cells. In order to get the cells back to their original order, you need to undo the filter.
You have to repeat this for each column with colored cells.
The first cell is not counted no matter what. Insert an empty row above all the other rows, and enter a name for the column before you apply the filter.
An alternative method
If you have time, and the number of cells you need to count isn’t that many, you can manually select them. To manually select cells, Ctrl+Click the cells you want to count and look at the bottom right to see the number of selected cells.
