Table of Contents
Conditional formatting is a feature in Microsoft Excel that allows users to apply specific formatting rules to cells based on certain conditions. One common use of conditional formatting is to highlight cells if they do not meet a certain criteria. In the case of comparing two cells, users can apply conditional formatting to highlight cells if they are not equal. This means that if the values in the two cells do not match, the formatting rule will be applied and the cells will be visually differentiated from the rest of the data. This can be a useful tool for identifying discrepancies or differences in data sets and can help users quickly identify and address any errors or inconsistencies.
Excel: Apply Conditional Formatting if Two Cells Are Not Equal
Often you may want to apply conditional formatting to two cells in Excel if their values are not equal.
The following example shows exactly how to do so.
Example: Conditional Formatting if Two Cells Are Not Equal
Suppose we have the following lists in Excel that contain the names of 10 students each:
Suppose we would like to highlight the rows where the two names are not equal between the classes.
To do so, highlight the values in the range A2:B11, then click the Conditional Formatting icon on the Home tab, then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type in the following formula into the box:
=$A2 <> $B2Then click the Format button and choose a fill color to use, then click OK:
Each row where the names are not equal will automatically be highlighted:
Note that this formula is case-insensitive. This means if we have “john” in one column and “John” in the other, the formula will assume these values are equal.
However, you can use the following formula to apply a case-sensitive search:
=NOT(EXACT($A2,$B2))
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-apply-conditional-formatting-in-excel-to-highlight-cells-if-two-cells-are-not-equal/
stats writer. "How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-apply-conditional-formatting-in-excel-to-highlight-cells-if-two-cells-are-not-equal/.
stats writer. "How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-apply-conditional-formatting-in-excel-to-highlight-cells-if-two-cells-are-not-equal/.
stats writer (2024) 'How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-apply-conditional-formatting-in-excel-to-highlight-cells-if-two-cells-are-not-equal/.
[1] stats writer, "How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I apply conditional formatting in Excel to highlight cells if two cells are not equal?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.