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.

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:

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.

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

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

④ In the format values box, type the following formula:

⑤ Next, click on Format.

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

⑦ Now, choose any suitable color and press OK.

Now, the formula and format are set.

⑧ Click on OK.

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:

📌 Steps 

① Firstly, select all the data, B5:C10.

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

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

④ After that, click on OK.

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:

📌 Steps

① First, select the entire data.

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

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

④ In the format values box, type the following formula:

⑤ Next, click on Format

⑥ From the Format Cells dialog box, select Fill.

⑦ Now, choose any color and click on OK.

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

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:

📌 Steps 

① Firstly, select all the data.

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

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

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

⑤  After that, click on OK.

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:

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.

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

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

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

⑤ Now, choose any color. Click on OK.

⑥ Click on Ok.

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

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

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

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

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

⑫ After that, click on Ok.

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

• How to Apply Different Types of Conditional Formatting in Excel
• How to Find External Links in Conditional Formatting in Excel
• How to Apply Conditional Formatting for Blank Cells in Excel
• How to Apply Conditional Formatting on Multiple Columns in Excel
  
• How to Remove Conditional Formatting in Excel
• How to Remove Conditional Formatting but Keep the Format in Excel
• How to Do Conditional Formatting in Excel
• Conditional Formatting with Formula in Excel

<< Go Back to Conditional Formatting | Learn Excel