Compare Two Cells Using Conditional Formatting in Excel (3 Methods)

We have a dataset of 3 columns titled Yearly Revenue Statement. The first column contains different unit names. The second and third column contain yearly revenues in dollars against the unit names. We’ll compare the revenue between the two years via conditional formatting.

Dataset to Compare Two Cells Using Conditional Formatting in Excel


Method 1 – Compare Two Cells Using Conditional Formatting to Highlight Matched Records in Excel

  • Select the two columns and go to the Home tab.

  • Click on the Conditional Formatting drop-down and choose New Rule from the drop-down list.

Compare Two Cells Using the Conditional Formatting and Highlight the Matched Records in Excel

  • A New Formatting Rule dialog box will appear.
  • Choose Use a formula to determine which cells to format from the Select a Rule Type section.
  • Under the Format values where this formula is true section, enter the following formula:
=$C5=$D5
  • Hit the Format button.

New formatting rule dialog box: Compare Two Cells Using the Conditional Formatting and Highlight the Matched Records in Excel

  • A Format Cells dialog box will appear.
  • Go to the Fill tab.
  • From the Background Color section, pick a color.
  • Press the OK button.

  • Press OK to close the formatting window, and the function will highlight all matching cells between columns.


Method 2 – Highlight Duplicates by Comparing Two Cells Using Conditional Formatting in Excel

  • Select the cells where you want to run the comparison.
  • Go to the Home tab.

  • Go to Conditional Formatting, choose Highlight Cells Rules, and pick Duplicate Values.

Highlight the Duplicates by Comparing Two Cells Using the Conditional Formatting in Excel

  • A Duplicate Values dialog box will appear.
  • Select Duplicate in the Format cells that contain box.
  • Select a color format in the values with box.
  • Press OK.

Duplicate values dialog box: Highlight the Duplicates by Comparing Two Cells Using the Conditional Formatting in Excel

  • All the cells with duplicates values have instantly highlighted with your selected color as in the picture below. Note that this will pick up duplicates across rows (i.e. if cells C5 and D10 match, they’ll be highlighted).


Method 3 – Compare and Highlight Unique Values from Two Cells Using Conditional Formatting

  • Select the cells where you want to run the comparison.
  • Go to the Home tab.

  • Go to Conditional Formatting, choose Highlight Cells Rules and select Duplicate Values.

Compare Two Cells Using the Conditional Formatting and Highlight the Unique Values in Excel

  • A Duplicate Values dialog box will appear.
  • Select Unique in the Format cells that contain box.
  • Select a color format in the values with box.
  • Hit OK.

  • All the cells with unique values will be highlighted with your selected color as in the picture below.

Read More: How to Compare Text in Excel and Highlight Differences


Download the Practice Workbook


<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

4 Comments
  1. doesnt work ! Colors all selected range cells equally even different ones !

    • Hey EXC,
      Thank you for your comment. I am replying on behalf of ExcelDemy. If you are facing this problem with Method 1 then you will have to select the reference cell in the formula from the first row of the range you are selecting. You can see that range C5:D12 is selected in this article. And, cells C5 and D5 were used in the formula. These cells are in the first row of the selected range. You will have to maintain this rule while writing the formula.
      I hope this will help you to solve your problem. And, if it doesn’t, let us know in which method you are facing the problem.
      Regards
      Mashhura Jahan
      ExcelDemy.

  2. I want to be able to compare two worksheets B cells and in Column C in worksheet number two give the difference in values of the two B cells. Example: Day 1 worksheet B6 value is 304. Day 2 worksheet B6 value is 412. In Column C, C6 want the value to be 108. (If Day 2 shows a lesser value than Day 1 it should show a (-) number.)

    • Hello John,

      To compare two worksheets follow the given steps:

      Day 1 (Worksheet 1):
      Cell B6: 304

      Day 2 (Worksheet 2):
      Cell B6: 412

      Cell C6 (Difference): 108

      The formula in cell C6 of Worksheet 2 would be:

      =Sheet2!B6 – Sheet1!B6

      This will display 108 in cell C6 as the difference between 412 (Day 2) and 304 (Day 1). If Day 2’s value is lower, the result would automatically show as a negative.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo