Excel: Colored cells count - how it works

You can count colored cells in Excel without much effort. Read here how exactly this works.

Count colored cells in Excel - that's what you have to do

1. Start Excel on your computer.
2. Switch to the "Formulas" tab and click on "Define name".
3. Now you can define a new formula Name it "Color" and add the property "= CELL.CONTRACT (24; INDIRECT (" ZS (-1) ";))" under "Refers to", if you want to count the font color.
4. If you would like to count the background color of the cell instead, you have to insert the property "= CELL.ASSIGN (63; INDIRECT (" ZS (-1) ";))". Save the entries with "OK".
5. Now insert the formula "= color" in the auxiliary column B1 and copy it down until you have reached the end of the colored cells.
6. You will now see the numbers for the respective colors in the auxiliary column (3 is e.g. red). To count a certain color, use the formula "= COUNTIF (B: B; 3)" - the "3" defines the color to be counted.

This practical tip is based on Excel 2013 and can be carried out almost identically with other versions. We will tell you in the next practical tip whether you can automatically color fields under Excel.