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.
How to Compare Two Tables and Highlight Differences in Excel: 4 Ways
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<>C5Here, 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…
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.
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.
- Secondly, bring up the New Formatting Rule dialog 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.
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:
- Firstly, select the cell range C5:C10.
- Secondly, bring up the “New Formatting Rule” dialog box.
- 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)=0We’re checking if our value from the Column C is in the Column F. 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.
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.
- 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 SubCode Breakdown
- We’re calling our Sub Procedure HighlightDifference. Then, we’re declaring our variable i 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 Column C.
- After that, we’ve got the IF statement. In that, we’re checking each value of the Column C with that of the Column F. 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.
The Macro dialog box will appear.
- Select, “HighlightDifference” and click on Run.
Consequently, we’ll see the differences are highlighted in the second table.
Practice Section
We’ve supplied practice datasets with each method in the Excel file.
Download Practice Workbook
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!
<< Go Back to Tables | Compare | Learn Excel
