Google Sheets: Highlight Cell if Value Exists in List


Often you may want to highlight a cell in Google Sheets if it contains a value that exists in a list.

Fortunately this is easy to do using the custom formula function in Google Sheets.

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 Google Sheets:

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, then click the Format tab, then click Conditional Formatting from the dropdown menu:

In the Conditional format rules panel that appears on the right side of the screen, click the Format cells if dropdown, then choose Custom formula is, then type in the following formula:

=COUNTIF($C$2:$C$6,A2)>0

Once you click Done, each cell in the All Teams list that exists in the Good Teams list will automatically be highlighted in light green:

Google Sheets highlight if in list

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Highlight Highest Value in Google Sheets
How to Highlight Lowest Value in Google Sheets
How to Highlight Blank Cells in Google Sheets

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *