Table of Contents
In Microsoft Excel, there is a feature that allows users to easily highlight a cell if its value exists in a specified list. This can be achieved by using the “Conditional Formatting” tool, which allows users to set rules for formatting cells based on their values. By selecting the Highlight Cells Rules” option and choosing “Equal To” from the drop-down menu, users can input the list of values and specify the desired formatting, such as a different font color or fill color. This feature is useful for quickly identifying and organizing data that matches certain criteria, making it an efficient tool for data analysis and management.
Excel: Highlight Cell if Value Exists in 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:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I highlight a cell if its value exists in a list in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-highlight-a-cell-if-its-value-exists-in-a-list-in-excel/
stats writer. "How can I highlight a cell if its value exists in a list in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-highlight-a-cell-if-its-value-exists-in-a-list-in-excel/.
stats writer. "How can I highlight a cell if its value exists in a list in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-highlight-a-cell-if-its-value-exists-in-a-list-in-excel/.
stats writer (2024) 'How can I highlight a cell if its value exists in a list in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-highlight-a-cell-if-its-value-exists-in-a-list-in-excel/.
[1] stats writer, "How can I highlight a cell if its value exists in a list in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I highlight a cell if its value exists in a list in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.