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.

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.

• 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:

• Hit the Format button.

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

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

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

• 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