Compare Two Tables and Highlight Differences in Excel (4 Methods)

In this article, we’re going to show you how to use Excel to compare two tables and highlight the differences. We’ve taken two tables that show the pricing of the same product in two shops. For each shop, we’ve 2 columns: “Item” and “Price”.

excel compare two tables highlight differences


Download Practice Workbook


4 Ways to Compare Two Tables and Highlight Differences in Excel

1. Using Not Equal (<>) Operator in Excel to Compare Two Tables and Highlight the Differences

In the first method, we’re going to use the Not Equal (“<>”) operator along with Conditional Formatting to compare two tables and highlight any differences.

Steps:

  • Firstly, select the cell range F5:F10.
  • Secondly, from the Home tab >>> Conditional Formatting >>> select New Rule…

The New Formatting Rule dialog box will appear.

  • Thirdly, select “Use a formula to determine which cells to format” from the Select a Rule Type: section.
  • After that, type the following formula in Edit the Rule Description: box.
=F5<>C5

Here, we’re checking if the value from cell F5 is not equal to that of cell C5. If it is TRUE then the cell will be highlighted.

  • Then, click on Format…

excel compare two tables highlight differences

The Format Cells dialog box will appear.

  • Click on the “Fill” tab.
  • Then, select a color from the Background Color: section.
  • After that, press OK.

  • Finally, click on OK.

excel compare two tables highlight differences

Thus, we’ve compared two tables in Excel and highlighted the differences.


2. Compare Two Tables and Highlight Differences by Utilizing Unique Formatting Rule

In this method, we’ll use the “format only unique values” option from the Conditional Formatting rule to highlight the differences between two tables in Excel.

Steps:

  • Firstly, select the full table cell range B4:F10.

excel compare two tables highlight differences

  • Secondly, bring up the New Formatting Ruledialog box.
  • Thirdly, select “Format only unique or duplicate values” from the Rule Type section.
  • Then, select “unique” from the Format all: box.
  • After that, select a background color using the Format… button.

  • Finally, click on OK.

excel compare two tables highlight differences

In conclusion, we’ve shown you another way to use Conditional Formatting to highlight the differences between the two tables.


3. Implementing the COUNTIF Function to Compare Two Tables and Highlight the Differences in Excel

For the third method, we’re going to use the COUNTIF function as a Conditional Formatting rule to highlight differences between two tables.

Steps:

excel compare two tables highlight differences

  • Thirdly, select “Use a formula to determine which cells to format” from the Select a Rule Type: section.
  • After that, type the following formula in Edit the Rule Description: box.
=COUNTIF(F5:F10,C5)=0

We’re checking if our value from the C column is in the F column. If it is not there, we’ll get 0. After that, we’re formatting the cells which are not found in the F5:F10 cell range.

Note: This formula will only work for unique values. Hence, if your table has duplicate values (for example, two shirts have the same price), do not use this method.

  • Then, pick a background color from the “Format…” button.
  • Finally, press OK.

Thus, we’ve highlighted the differences between the two tables in Excel.

excel compare two tables highlight differences


4. Using VBA in Excel to Compare Two Tables and Highlight the Differences

For the last method, we’ll use Excel VBA to compare two tables and highlight the differences.

Steps:

  • Firstly, from the Developer tab >>> select Visual Basic.

This will bring up the Visual Basic window.

  • Secondly, from Insert >>> select Module.

excel compare two tables highlight differences

  • Thirdly, type the following code.
Sub HighlightDifference()
Dim i As Long
    For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row
        If Cells(i, "C") <> Cells(i, "F") Then
            Cells(i, "F").Interior.Color = vbYellow
        End If
    Next i
End Sub

Code Breakdown

  • We’re calling our Sub Procedure HighlightDifference. Then, we’re declaring our variablei” as Long.
  • Then we’ve got a “For loop”. With the End(xlUp) we’re going to go through the last row with data in the C column.
  • After that, we’ve got the IF statement. In that, we’re checking each value of the C column with that of the F column.  If there is any value that doesn’t match, we’ll use the Interior.Color property to change the color of the cell. We’ve used the color vbYellow here. This process will continue until the last row.

  • After that, Save the Module and close the window.
  • Then, From the Developer tab >>> select Macros.

excel compare two tables highlight differences

The Macro dialog box will appear.

  • Select, “HighlightDifference” and click on Run.

Consequently, we’ll see the differences are highlighted in the second table.

excel compare two tables highlight differences


Practice Section

We’ve supplied practice datasets with each method in the Excel file.


Conclusion

We’ve shown you 4 methods in Excel to compare two tables and highlight the differences. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo