Skip to Content

How do I count the number of cells with color text in Excel?

By: Author Color With Leo

Color coding cells in Excel can be a useful way to highlight important data or categorize information. But once you’ve applied color formatting, you may need to get a count of how many cells have color text in order to analyze the data properly.

Counting colored cells in Excel is easy using built-in functions and features. In this article, I’ll show you several methods to count colored cells so you can analyze and work with color coded data more effectively.

Why Count Colored Cells in Excel?

Here are some common reasons you may want to count cells with colored text in Excel:

  • See how many data points are marked as “high priority” or “need follow up” using color coding
  • Get a count of error values highlighted in red color
  • Count how many past due tasks are highlighted in yellow
  • Tally up items categorized by color for analysis
  • Count color coded values as part of conditional formatting rules
  • Get a quick visual sense of proportions among colored cells

Using Conditional Formatting

One of the easiest ways to count cells with colored text is to use Conditional Formatting in Excel. Here are the steps:

  1. Select the cells you want to count.
  2. On the Home tab, click Conditional Formatting > New Rule.
  3. Select the “Format only cells that contain” option.
  4. Set the rule to format only cells with color text.
  5. Click the Format button and select the Fill color you want to count.
  6. OK to create the rule.
  7. The selected cells will be formatted with color fill for only cells containing color text.
  8. You can now see at a glance how many cells are color coded and get an exact count.

Using Special Cells to Filter by Font Color

Another handy way is using Excel’s Special Cells function for font colors:

  1. Select the range of cells you want to count.
  2. On the Home tab, click Find & Select > Go To Special.
  3. Select “Cells with fonts in color” and OK.
  4. Only the color text cells will remain selected.
  5. Press Ctrl+G to open the Go To dialog and view the selected cell count.

Filtering on Font Color

You can also use cell filtering to count colored text:

  1. Select your data range and apply filtering by clicking Filter on the Data tab.
  2. Open the filter dropdown for the color text column.
  3. Uncheck Select All, then check the box next to the Font Color you want to count.
  4. Only those color formatted cells will display.
  5. The record count number provides the count of visible color coded cells.

Using COUNTIF Formula for Font Color

The COUNTIF formula can count cells based on formatting criteria like font color. Using the formula is a flexible way to get a color text count:

  1. Add this COUNTIF formula in a new cell:
    =COUNTIF(range,”*”+fontcolor(A1)+”*”)
  2. Where “range” is the cells to count and A1 is a cell with the color text you want to count.
  3. The fontcolor piece pulls the hex code for the font color into the criteria.
  4. You can add multiple COUNTIF formulas for different color counts.

Using Subtotals for Color Coded Data

If your color coded data is sorted into groups, the Subtotal feature can give you a count per color:

  1. Sort your data range by the color text column so like colors group together.
  2. Click Data > Subtotal.
  3. In the Subtotal box, choose At each change in the color column.
  4. Check Count below the column you want to subtotal.
  5. OK to generate the subtotals showing counts per color.

Examples of Counting Colored Cells in Excel

Below are some examples of counting colored cells using the methods outlined above:

Conditional Formatting Example

Using conditional formatting to visually count cells with red error values:

Data Values
Value 1 100
Value 2 #N/A
Value 3 105
Value 4 #DIV/0!
Value 5 102

Conditional formatting applied to color code error values red. The visual count is 2 red cells.

Subtotals Example

Using Subtotals to count tasks by status color coding:

Task Status Owner
Sign contract Complete John
Create invoice Overdue Mary
Send payment Complete Linda
Follow up on order Pending Mike

Subtotals used to count Status by color:

Status Count
Complete 2
Overdue 1
Pending 1

Tips for Counting Colored Cells in Excel

Here are some additional tips for counting cells with colored text efficiently:

  • Use filtering or conditional formatting for a quick visual count by color.
  • Leverage COUNTIF or Subtotals when you need exact color counts.
  • Make sure your color formatting is consistent before counting.
  • Put color count formulas in their own column for easy updating.
  • Format your color count cells to match the colors being counted.
  • Use color scales in conditional formatting to see color proportions.

Next Steps

After mastering techniques for counting color coded cells, some next things to learn include:

  • Creating color coded lookups – Make Excel color code data automatically based on keyword, number range, or other criteria.
  • Using color to highlight duplicates – Apply rules to color duplicate values for easy visual analysis.
  • Linking color to data bars – Show color coordinated data bars to visualize relative proportions.
  • Mapping color codes to icons – Associate color categories with representative icons for reporting.

Conclusion

Color coding your Excel data makes it easier to categorize and count values for insights. By using Conditional Formatting, Special Cells, Filtering, COUNTIF, and Subtotals, you can get counts of cells based on font color. With a few simple steps, you can analyze and work with your color coded data in new visual ways.

So explore the options above and start putting colors to work in Excel today. Counting colored text is an easy way to unlock more value from highlight color formats.