silikonjeans.blogg.se - Compare two columns in excel conditional formatting

In case we want to do it in a case sensitive way, the following formula would do: = NOT(EXACT(A1,F1))ĮXACT takes two arguments. 🙂Īt this point, you may notice that the formula we used in the above example is case insensitive. We just need to pay attention to where we put the $. When you get this clear, using formula in conditional formatting is secret no more. In H10, whether C10 H10, which is FALSE => not formatted This image illustrates why H2 is highlighted Until it checks the last cell in the range, i.e. In H2, it checks if C2H2, which is TRUE => formatted In H1, it checks if C1 H1, which is FALSE => not formatted In G1, it checks if B1 G1, which is FALSE => not formatted In F1, it checks if A1 F1, which is FALSE => not formatted Since both A1 and F1 are relative, it moves across and down with the active cell. Imagine this, for the range F1:H10 there are invisible formulas behind the scenes to determine if a cell should be formatted or not. It would be easier to “visualize” it indeed: In plain English, these rules ask Excel to look into the range of F1:H10, compare the corresponding cell value in the range starting from A1, cell by cell, then highlight the differences (this is where the formula =A1F1 plays the role).

And the “Applied to” ($F$1:$H$10, this range is both column and row absolute), which is where we selected before clicking the Conditional Formatting button,.

Both A1 and F1 are the upper leftmost cell in the two ranges.

Both A1 and F1 are relative references (both column and row), i.e.

Please pay attention to the formula we used: = A1 F1 The main reason for the failure is the use of $.

Pick the format(s) you like (in this example, light yellow filled)Īlthough it seems to be easy, many people failed to do it at first attempt.

Type “ =A1F1” (without double quotation marks).

Select “Use a formula to determine which cells to format (isn’t it self-explanatory?).

Select the range you’d like to highlight where differences are (note: the range selected should be of the same size as the comparison range).

If you prefer reading to watching, please continue…