Table of Contents
In Excel, you can highlight cells in a column that contain values that exist in a list by using the Conditional Formatting feature. Select the column of cells to be highlighted, open the Conditional Formatting menu, and select New Rule. Select the ‘Use a formula to determine which cells to format’ option and enter the formula ‘=COUNTIF(list,A1)’ and select the fill color to be applied to the cell. This will highlight any cell in the column that contains a value that exists in the list.
Often you may want to highlight a cell in Excel if it contains a value that exists in a 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 Exists 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 exists 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 green:
Lastly, click OK.
Each cell in the All Teams list that exists in the Good Teams list will automatically be highlighted in light green:
