How to Count Cells by Color in the New Excel 2007 Table

You usually use some subjective Excel Filter criteria in cells like: red = done, yellow = pending, orange = warnings, etc. Other times the criteria may be accompanied by a legend.

Coloring Excel Cells is a great way to store information and to analyze data visually without using helper columns.

This is very simple with new Excel 2007 Table; here’s how to filter by color…

1) Convert your Data Region into a Table

  • Press CTRL+T or
  • Go to Insert>Tables>Table

Place inside the data region and then proceed this way…

2) Apply Excel Filter by Color

See graphic 1.

Click the respective arrow in the column header.

The option “Filter by color” will be activated if at least one cell of the column is colored.

Now, you can apply step 3 below…

Graphic 1

3) Add the Total Row

    • Go to Design>Table style options>Total row or
    • Press CTRL + SHIFT + T

See the Total row in row 27 (graphic 1)

Activate the Contextual Design Ribbon by placing inside the table; then…

4) Count Filtered Items with Total Row and Count Function

Click the arrow at the Total row and select the desired function: Choose Count or Count Numbers and done.

Important notice

  • Some colors are not available in previous than 2007 versions
  • Fields without colored cells show this option disabled
  • You can only apply one filter color at a time; for example you cannot filter red and yellow
  • Colors of Table styles are not taken into account