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.
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.
In the example, we have used the following formula to highlight all cells that have “Morning” in the range C4-C10.
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.
=EXACT (C4, $F$5)