Rafiul Haq
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”.
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<>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.
Read More: How to Make a Comparison Table in Excel (2 Methods)
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.
Read More: How to Compare Two Tables for Differences with Excel VBA (3 Methods)
Similar Readings
- COUNTIF Date Is within 7 Days
- How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)
- COUNTIF Greater Than and Less Than [with Free Template]
- How to Use COUNTIF Between Two Numbers (4 Methods)
- VBA to Loop through Rows of Table in Excel (11 Methods)
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 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.
Read More: COUNTIF Excel Example (22 Examples)
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 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.
The Macro dialog box will appear.
- Select, “HighlightDifference” and click on Run.
Consequently, we’ll see the differences are highlighted in the second table.
Read More: VBA COUNTIF Function in Excel (6 Examples)
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!
Related Articles
- COUNTIF between Two Cell Values in Excel (5 Examples)
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
- Count Blank Cells with Excel COUNTIF Function: 2 Examples
- How to Use COUNTIF with WEEKDAY in Excel
- Excel VBA: Insert Data into Table (4 Examples)
- How to Create a Table Array in Excel (3 Methods)
