Table of Contents
Often you may want to highlight a cell in Excel if it does not contain a value in some list.
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: How to Highlight Cell if Value Not in List
Suppose we have the following two lists of basketball teams in Excel:
Suppose we would like to highlight each cell in the All Teams list that does not exist in the Good Teams list.
To do so, we can highlight the cell range A2:A11 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($C$2:$C$6,A2)=0
Then click the Format button and choose a color to use for the conditional formatting.
We’ll choose light red:
Lastly, click OK.
Each cell in the All Teams list that does not exist in the Good Teams list will automatically be highlighted in light red:
