How to Use Conditional Formatting to Compare Two Columns in Excel

Conditional formatting allows you to change the appearance of cells based on specific criteria that you define. When the conditions are met, the cell range is formatted according to your specifications. Conversely, if the conditions are not satisfied, the cell range remains unformatted. While Excel provides several built-in conditions, you can also create custom rules.

In this example, we’ll compare two columns and use conditional formatting to highlight matching or duplicate values.

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

Step-by-Step Guide

  • Select the entire data range containing the two columns you want to compare. For instance, let’s say we have movie names in columns B and C (cells B5:C10).

select the range of cells of two columns

  • Go to the Home tab in Excel.
  • Click on Conditional Formatting and choose New Rule.

go to conditional formatting option in excel

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

select conditional formatting option

  • In the Format values where this formula is true field, enter the following formula:
=$B5=$C5
    • This formula checks if the value in column B (cell B5) is equal to the value in column C (cell C5) for each row.

type the formula and format

  • Click on the Format button.
  • In the Format Cells dialog box, navigate to the Fill tab.

choose any color to use conditional formatting

  • Choose a suitable color to highlight the matching values (e.g., green) and click OK.

click ok to use the formatting

  • Confirm the formula and formatting settings by clicking OK in the New Formatting Rule dialog box.

using conditional formatting on two columns

Now, your rows with matching data will be visually formatted according to your chosen color. You’ve successfully used conditional formatting to compare two columns in Excel!


Method 2 – Use Conditional Formatting for Matching Values in Different Row in Excel

Sometimes, we encounter situations where the same values appear in two different columns but occupy different rows. In such cases, we want to compare these two columns and highlight any duplicate values using conditional formatting.

Here’s how you can achieve this:

dataset to compare two columns

Step-by-Step Guide

  • Select the entire data range that includes the two columns you wish to compare. For instance, let’s assume we have movie names in columns B and C (cells B5:C10).

select the range of cells

  • Go to the Home tab in Excel.
  • Click on Conditional Formatting and choose Highlight Cells Rules, then select Duplicate Values.

choose the highlight cells rules from conditional formatting in excel

  • In the Duplicate Values dialog box, you can pick any fill color to highlight the matching values. If you have specific preferences, use the Custom Format option to select your preferred colors.

choose your colors

  • Click OK to confirm your choices.

conditional formatting done in excel for two columns

As a result, all matching values between the two columns will be visually highlighted. You’ve successfully used conditional formatting to identify duplicate values in different rows.


Method 3 – Use Conditional Formatting for Unique Values from Two Columns but the Same Row

Sometimes, you’ll encounter unique values in two different columns within the same row. To identify and highlight these unique values, we are going to use this dataset and follow these steps:

dataset to use conditional formatting

Step-by-Step Guide

  • Select the entire dataset that includes the two columns you want to compare. For instance, let’s assume we have movie names in columns B and C (cells B5:C10).

select the entire data

  • Go to the Home tab in Excel.
  • Click on Conditional Formatting and choose New Rule.

select new rules from conditional formatting

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

select conditional formatting option

  • In the Format values where this formula is true field, enter the following formula:
=$B5 <> $C5
    • This formula checks if the value in column B (cell B5) is different from the value in column C (cell C5) for each row.

select the option and click format

  • Click on the Format button.
  • In the Format Cells dialog box, navigate to the Fill tab.

choose any color to use conditional formatting

  • Choose any color you prefer to highlight the unique values (e.g., yellow) and click OK.

click ok to apply the conditional formatting in excel

  • Confirm the formula and formatting settings by clicking OK in the New Formatting Rule dialog box.

Compare two columns and use conditional formatting in excel

Now, your cells with unique data in the same row will be visually formatted according to your chosen color.


Method 4 – Compare Two Columns and Find Unique Values from Two Columns and Different Rows

To identify all unique values from two columns across all rows, we are going to use this dataset and follow these steps:dataset to compare two columns

Step-by-Step Guide

  • Start by selecting the entire dataset.

select the entire data

  • Go to the Home tab in Excel.
  • Click on Conditional Formatting and choose Highlight Cells Rules, then select Duplicate Values.

select duplicate values from highlighting cells rules

  • In the Duplicate Values dialog box, select the Unique option from the drop-down menu.

select unique option to compare

  • You can choose any fill color to highlight the unique values. If you have specific preferences, use the Custom Format option.

choose any colors to use conditional formatting in excel

  • Click OK to confirm your choices.

result of compare two columns and use conditional formatting in excel

As a result, all unique values from the two columns will be visually highlighted across different rows. You’ve successfully used conditional formatting to find and emphasize unique data!


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

In this scenario, we’ll compare two columns that are not adjacent to each other. Specifically, we want to compare a column with a value from a different column. Let’s use the following dataset as an example:

dataset to use conditional formatting and compare two columns in excel

  • Dataset:
    • We have scores for some students.
    • The passing mark is 33.
    • To achieve an A+, students need to score 80 or higher.

Steps

  • Select all the marks from your dataset.

select the entire marks of two columns to compare in excel

  • Go to the Home tab in Excel.
  • Click on Conditional Formatting and choose More Rules.

go to more rules from conditional formatting to compare two columns

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

new formatting rules dialog box to use conditional formatting in excel

  • Choose Between from the dropdown menu.
  • Enter the values 33 and 79 (you can also use cell references).
  • Click on Format.

enter the two marks to compare from two columns

  • Choose a color (e.g., yellow) and click OK.

choose any color to use conditional formatting

  • Confirm your choices by clicking OK again.

click on ok to use conditional formatting

You will see highlighted marks between 33 and 79.

highlighted cells of the columns

  • Select all the marks again.

select all the marks again to compare

  • Go to Conditional Formatting and select More Rules.

select more rules from highlight cells rules

  • Choose greater than or equal to from the dropdown menu.

select greater than or equal to option

  • Enter the value 80 (or use the cell reference for A+).
  • Format the fill color as green.

enter the value to use conditional formatting in excel

    • Click OK.

result of compare two columns using conditional formatting in excel

Now, your marks between 33 and 79 will be highlighted in yellow, and A+ scores (80 or more) will be highlighted in green. You’ve successfully used conditional formatting for different score ranges in Excel!


Additional Tips

  • If you add new rows, remember to adjust the range for formatting rules.
  • Be cautious when selecting cell ranges; incorrect choices can affect the behavior of conditional formatting.

Download Practice Workbook

You can download the practice workbook from here:


Further Readings


<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo