Using Conditional Format to Compare Two Columns in Google Sheets

Today we are going to specifically look at how to compare two columns using conditional formatting in Google Sheets.

Let’s get started.

2 Scenarios Where We Compare Two Columns Using Conditional Formatting in Google Sheets

1. Exact Match Rows Between Two Columns using Conditional Formatting in Google Sheets

We start with something simple, that is to highlight the exact row matches between two columns in Google Sheets.

Let’s establish the formula before we apply the conditional formatting for the following dataset.

dataset to compare two columns with conditional formatting in google sheets

For our test case to be TRUE, both cells in each row must match. This can be done with a simple “equal-to” operation:


Do this for the rest of the rows.

testing exact row match between two columns in google sheets

Now that we have established the logic of our comparison, we can move on to apply conditional formatting to highlight the matching rows.

We first select the range of data, cell B3 to C12, and navigate to the Conditional Formatting menu. Format > Conditional formatting

navigating to conditional formatting from the format tab

In the conditional formatting rules menu, set up the following conditions:

  1. Make sure that the range selected covers all the values that are to be formatted.
  2. Select the “Custom formula is” option from the Format rules section.

set up for conditional formatting

Here we apply the custom formula:


applying conditional formatting to compare two columns for matching rows in google sheets

The absolute ($) added in front of column B and C references mean that the columns are fixed whereas the row references are free to move, thus the formatting is painted across the entire row.

As we can see, the matching rows of the two columns have been highlighted. To learn more about this method and much more, please visit our Google Sheets: Conditional Formatting Row Based on Cell article.

2. Compare Between Two Columns to Highlight Occurrences

Comparison between two columns is not limited to exact row matches but much more. One of the other ways that we use column comparison is to find the number of occurrences.

For our example, we will try to find the presence of the Strain from a Sample. This is summarized in the following dataset:

second dataset to compare two columns with conditional formatting in google sheets

Let’s first develop our formula. We are comparing two columns in Google Sheets where we will match the occurrences of values in one column in the other. For this, we require the COUNTIF function.


using countif function to count occurences

For any matches found, the count value will be 1. But since conditional formatting takes a Boolean value, we must add an IF condition to the formula. This can simply be done by inserting the “>0” condition.


using countif function and if condition to compare two columns

Now, let’s transfer our formula to conditional formatting as a custom formula. Make sure the application range is correct as well.

using conditional formatting to compare two columns in google sheets to count occurrences

We have correctly compared two columns and highlighted the occurrences of values of one column in Google Sheets.

The same formula can be applied to multiple columns to highlight matching data in them as well. The only difference is the range of application:

applying our formula to multiple columns

Read More: Compare Two Columns for Duplicates in Google Sheets (3 Easy Ways)

Final Words

That concludes all the ways we can use conditional formatting to compare two columns in Google Sheets.  However, if you are looking to know beyond the conditional formatting constraints and more about comparing two columns, please see our Compare Two Columns in Google Sheets article.

We hope that our discussion has come in handy. Please feel free to leave any queries or advice you might have in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply