Spreadsheet highlight common cell values between two columns


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

spreadsheet with two columns with values/ids

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.

conditional formatting applied on 3rd column

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

image showing values in 2nd column being highlighted

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’.

  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