How to Compare Two Columns Using Conditional Formatting in Excel

One of the essential tools of Excel is Conditional Formatting.  Conditional formatting highlights interesting cells or ranges of cells, indicates significant values, and visualizes data with the help of data bars, color scales, and icon sets that resemble precise shifts in the dataset. It also helps you visually investigate and interpret your dataset, identify important issues, and distinguish between models and trends.
In this tutorial, you will learn how to compare two columns using conditional formatting in Excel.


Download Practice Workbook

Download the following practice workbook.


Compare Two Columns Using Conditional Formatting for Matching Values

Conditional formatting changes the presentation of cells on the support of requirements that you define. If the conditions are true, the cell range is formatted as specified; if the conditions are false, the cell range is not formatted. There are many built-in conditions. However, you can also create your own.

Here, we are going to compare two columns and use conditional formatting for matching or duplicate values in Excel.

1. Compare Two Columns for Matching Values in the Same Row

To demonstrate this, we are going to use this dataset:

dataset to compare two columns and use conditional formatting in excel

Here, we have some movie names. Our goal is to compare two columns and highlight those rows having matching values.

📌 Steps

First, select the entire data, B5:C10.

select the range of cells of two columns

Then, from the Home tab. Select the Conditional Formatting > New Rule.

go to conditional formatting option in excel

From the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

select conditional formatting option

In the format values box, type the following formula:

=$B5=$C5

type the formula and format

Next, click on Format

From the Format Cells dialog box, select the Fill tab.

choose any color to use conditional formatting

Now, choose any suitable color and press OK.

click ok to use the formatting

Now, the formula and format are set.

Click on OK.

using conditional formatting on two columns

As you can see, we are finally successful in formatting rows for matching data.

Read more: How to Compare Two Columns in Excel For Finding Differences

2. Use Conditional Formatting for Matching Values in Different Row in Excel

In some cases, we have the same values in two different columns and different rows. We are going to show you how to compare those two columns and use conditional formatting for duplicate values.

To demonstrate this, we are going to use this dataset:

dataset to compare two columns

📌 Steps 

Firstly, select all the data, B5:C10.

select the range of cells

Now, from the Home tab, select Conditional Formatting > Highlight Cells Rules > Duplicate Values.

choose the highlight cells rules from conditional formatting in excel

From the Duplicate Values dialog box, select any fill colors. You can choose your preferred colors from the Custom Format option.

choose your colors

After that, click on OK.

conditional formatting done in excel for two columns

In the end, you will see all the matching values from the two columns. And all those are highlighted.


Similar Readings:


Analyze Two Columns and Use Conditional Formatting for Unique Values in Excel

1. Use Conditional Formatting for Unique Values from Two Columns but the Same Row

There may be unique values in two different columns. We will use a formula to find them and highlight the unique values in the same row.

To demonstrate this, we are going to use this dataset:

dataset to use conditional formatting

📌 Steps

First, select the entire data.

select the entire data

Then, from the Home tab. Select the Conditional Formatting > New Rule.

select new rules from conditional formatting

From the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

select conditional formatting option

In the format values box, type the following formula:

=$B5 <> $C5

select the option and click format

Next, click on Format

From the Format Cells dialog box, select Fill.

choose any color to use conditional formatting

Now, choose any color and click on OK

click ok to apply the conditional formatting in excel

Your formula and format are set. Now click on OK.

Compare two columns and use conditional formatting in excel

As you can see, we are successful in formatting cells from different columns for unique data.

2. Compare Two Columns and Find Unique Values from Two Columns and Different Rows

We are going to find all the unique values from two columns from all the rows.

To demonstrate this, we are going to use this dataset:

dataset to compare two columns

📌 Steps 

Firstly, select all the data.

select the entire data

Now, from the Home tab, select Conditional Formatting > Highlight Cells Rules > Duplicate Values.

select duplicate values from highlighting cells rules

From the Duplicate Values dialog box, select the Unique option in the drop-down menu.

select unique option to compare

Now,  select any fill colors. You can choose your preferred colors from the Custom Format option.

choose any colors to use conditional formatting in excel

  After that, click on OK.

result of compare two columns and use conditional formatting in excel

In the end, you will see all the unique values from the two columns. And all those are highlighted.


Compare Two Columns and Use Conditional Formatting for Greater or Less than a Value

Here, we are going to compare two columns. But this time, the columns won’t be adjacent to each other. We will compare a column with a value from a different column.

To demonstrate this, we are going to use this dataset:

dataset to use conditional formatting and compare two columns in excel

Here, we have some scores of some students. Our passing mark is 33. And to get A+, you have to score 80 or more than that.

If the score is between 33 and 79, we will highlight them with the yellow fill color.

And if the score is 80 or more, we will fill them with green color.

📌 Steps

Firstly, select all the marks from the dataset.

select the entire marks of two columns to compare in excel

From the Home tab, go to Conditional Formatting > Highlight Cells Rules > More Rules.

go to more rules from conditional formatting to compare two columns

From the New Formatting Rule dialog box, select Format only cells that contain.

new formatting rules dialog box to use conditional formatting in excel

Then, select Between from the dropdown menu. Enter the values 33 and 79 (You can use cell references also).  After that, click on Format.

enter the two marks to compare from two columns

Now, choose any color. Click on OK.

choose any color to use conditional formatting

Click on Ok.

click on ok to use conditional formatting

After that, you will see highlighted marks between 33 and 79.

highlighted cells of the columns

To highlight the mark of A+, select all the marks again.

select all the marks again to compare

From the Home tab, go to Conditional Formatting > Highlight Cells Rules > More Rules.

select more rules from highlight cells rules

Then, select greater than or equal to from the dropdown menu.

select greater than or equal to option

Enter the value 80 here or use the cell reference of A+. Format the fill color with green.

enter the value to use conditional formatting in excel

After that, click on Ok.

result of compare two columns using conditional formatting in excel

In the end, we are successful in using conditional formatting for different ranges of marks in excel.


💬 Things to Remember

✎ If you add new rows, you have to change the range to format from the Manage Rules option.

✎ Conditional formatting can act differently if you choose the wrong range of cells.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to compare two columns by using conditional formatting in  Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo