Table of Contents
Google Sheets is a powerful tool that allows users to create and manipulate spreadsheets. One useful feature of Google Sheets is the ability to highlight a cell if its value exists in a specific list. This is helpful for quickly identifying data that meets certain criteria. To use this feature, the user must first create a list of values in a separate sheet or column. Then, using conditional formatting, the user can specify the range of cells to be checked and the criteria for highlighting. This feature allows for efficient data analysis and organization in Google Sheets.
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:
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: