Scenario

Suppose we are having two columns in a spreadsheet and we are trying to find the common values between them. That is highlight column values if they are present in another column.

For example lets say we have some ids in two different columns. Both columns have some data in common and some data exclusive to each other. But our focus here is to highlight the values in second column if they are present in first column and vice-versa.

To do this we are going to use conditional formatting, as the name suggests it will format the cells based on a condition. Here we are going to specify our own condition.

Look at the image below

Here we want to highlight the values in column ‘D’ if they are present in column ‘A’.

So to do that, first select the column i.e column ‘D’ and then go to conditional formatting. Go to Format->Conditional -> Condition->Formula (choose from dropdown) in the input box enter the formula COUNTIF($A:$A, $D1) like in the image below.

After you click ‘OK’ you can see that 2nd column values that are present in column 1 will be highlighted like below.

Now to do the reverse i.e to highlight values in first column from 3rd column we need to do apply similar formula COUNTIF($D:$D, $A1) on column ‘A’.

Note

1. You can remove conditional formatting rule that has been applied
2. For this formulae to apply there should be common values between these columns.
3. We can choose custom formatting like different color, size etc for conditional formatting.

You can also watch video to see how it is done in Google Spreadsheet