Highlight cells that equal

Highlight Cells that Equal in Values Using Formula

You may find it hard to extract a set of the same values from a large Excel sheet. To solve this issue, use the following excel formula to highlight cells that equal values.

Basic Formula

=A1="X"

Explanation

This formula returns TRUE if one or more cells have the text you have mentioned. Based on it you can highlight cells with equal text.

Important note: MS Excel has built-in presets to highlight cells with conditional formatting. It also features a preset that highlights cells containing a specific value. However, you can use your own formula to do this. We’ll explain the said formula in this article.

Example

In the example, we have used the following formula to highlight all cells that have “Morning” in the range C4-C10.

=C4="Morning"

Figure 1. Example of highlighted cells that equal a certain value

Note: The formula entered should be relative to the active cell. Here, it is C4.

In our example, the value which we want to highlight is pulled in the cell F5 to change it easily. The formula used by the new conditional formatting rule is

= C4 = $F$5

This formatting enables us to highlight cells that equal values using own formula.

What This Formula Actually Does?

The formula uses equal to operator (=) to make the comparison.

When the formula is used for the implementation of conditional formatting, it calculates the active cell in the selection at the time of the creation of the rule. In the example, the rule evaluates all the cells from C4 to C10. During the evaluation process, C4 is replaced with the active cell.

The search string (F5) is saved in the cell and address of the said cell. It is an absolute reference ($F$5). The reference is locked and does not change when the formula goes on to the check the next cell.

Case Sensitive Comparison

The comparison shown in the previous example is not case sensitive by default. The EXACT function can be used to ensure case sensitivity.

For Example

=EXACT (C4, $F$5)

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar