Excel: How to Highlight Values that Appear 3 Times

Often you may want to highlight values in a dataset in Excel that appear 3 times.

Fortunately this is easy to do using the New Rule feature within the Conditional Formatting options.

The following example shows how to do so.

Example: Highlight Values that Appear 3 Times in Excel

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to highlight all team names that appear 3 times.

To do so, we can highlight the cell range A2:A14 and then click the Conditional Formatting icon on the Home tab along the top ribbon, then click New Rule from the dropdown menu:

In the new window that appears, click Use a formula to determine which cells to format and then type the following formula in:

=COUNTIF($A$2:$A14,$A2)=3

Then click the Format button and choose a color to use for the conditional formatting.

We’ll choose light green:

Excel highlight values that appear 3 times

Lastly, click OK.

Each team name that appears 3 times will now be highlighted:

In this particular dataset, the team names “Mavs” and “Nets” both appear 3 times, so each of these team names is highlighted while all other team names are left untouched.

Note that if you’d like to highlight the entire row for each team that occurs 3 times, you can simply highlight the cell range A2:C14 before clicking the Conditional Formatting icon and typing in the new rule.

This will allow you to apply the conditional formatting to the entire row of each team that occurs 3 times:

Note: In this example we chose to use a light green fill for the conditional formatting of the cells, but you can choose any fill color you’d like.