How to Compare Two Columns Using Conditional Formatting in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Using Conditional Formatting to Compare Two Columns 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.


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.


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.


Download Practice Workbook

Download the following practice workbook.


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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo