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

The color of the font may be meaningful in your Data analysis with Excel Filter, for example: red font= error, yellow font= spending on the limit, etc.

That’s not all; you can use conditional formatting to apply colors to fonts depending on any given criteria.

Additionally, you can combine fill color with font color to expand the information that a single cell may store.

Coloring fonts in cells is a great way to store and analyze information visually without the use of helper columns.

Proceed this way…

1) Convert your Data Region into a Table

Select one cell of the data region and pres CTRL+T or go to Insert>Tables>Table

2) Apply Excel Filter by Font Color

See graphic 1.

Click the respective arrow in the column header.

If the field contains cells with colored font then the option will be activated.

Graphic 1

3) Show the Total Row

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

Excel 2007 shows a contextual Ribbon called Design. This is activated when you select a cell of the Table. Once the Design Ribbon is shown, proceed this way…

See row 27 in graphic 1 above

4) Count Filtered Cells with Count Function in the Total Row

See graphic 1.

Click the arrow at the Total row, choose the appropriate function: Count or Count Numbers.

You are done.

Become aware of…

  • The Filter by color option is shown disabled when there are no cells with font color
  • Font type is not eligible from Filters
  • Some Font colors are not available in versions older than Excel 2007
  • You can only apply one font color filter at a time; for example you cannot Filter red font and yellow font