How to Compare Two Tables in Excel for Data Differences

Comparing data across two tables in Excel is a common task for data analysis and management. Identifying discrepancies or matches between datasets can be crucial for various purposes. Excel offers powerful built-in features, particularly conditional formatting combined with lookup functions, to efficiently compare two tables and highlight any differences. This guide will walk you through the steps to Compare Two Tables In Excel, focusing on using formulas to pinpoint data variations.

Using Conditional Formatting with VLOOKUP to Highlight Differences

Conditional formatting is a fantastic Excel tool that allows you to automatically format cells based on specific criteria. When combined with the VLOOKUP function (or similar lookup functions), it becomes a robust method for comparing data in two tables. Here’s how to set it up:

Step 1: Open your Excel Worksheet

Begin by opening the Excel worksheet that contains the table you want to compare. Ensure that both tables you intend to compare are accessible within your Excel workbook, potentially on different sheets.

Step 2: Select the Starting Cell for Comparison

Choose the first cell in the table where you want to initiate the comparison. This cell will be the anchor point for applying the conditional formatting rule across your data range.

Step 3: Navigate to Conditional Formatting

On the Excel ribbon, go to the “Home” tab. In the “Styles” group, you will find the “Conditional Formatting” dropdown menu. Click on it to expand the options.

Step 4: Create a New Rule Using a Formula

From the “Conditional Formatting” menu, select “New Rule”. This action will open the “New Formatting Rule” dialog box, presenting various rule types. Choose the option “Use a formula to determine which cells to format”. This is where you will input the formula to compare your tables.

Step 5: Enter the VLOOKUP Formula

In the formula input box, you’ll need to enter a formula that checks if the value in the current cell exists in the second table. Let’s assume you are comparing data in ‘Sheet1’ with data in ‘Sheet2’, and you are comparing based on values in column A in both sheets. The formula would look like this:

=IF(ISERROR(VLOOKUP(A1, Sheet2!$A$1:$A$100, 1, FALSE)), TRUE, FALSE)

Explanation of the Formula:

VLOOKUP(A1, Sheet2!$A$1:$A$100, 1, FALSE): This part attempts to find the value of A1 (the current cell in Sheet1) within the range $A$1:$A$100 of ‘Sheet2’ (column A, rows 1 to 100). FALSE ensures an exact match.
ISERROR(...): If VLOOKUP cannot find a match, it returns an error (#N/A). ISERROR checks for this error.
IF(ISERROR(...), TRUE, FALSE): If ISERROR is TRUE (meaning no match was found in Sheet2), the IF function returns TRUE. Otherwise, it returns FALSE.
When the formula returns TRUE, the conditional formatting will be applied to the cell.

Important:

A1: This refers to the first cell you selected in Step 2. Excel will automatically adjust this reference as it applies the conditional formatting to other cells in your selection.
Sheet2!$A$1:$A$100: This is the range of cells in ‘Sheet2’ that you are comparing against. Adjust Sheet2, column A, and the range $A$1:$A$100 to match the actual sheet name, column, and data range of your second table. The $ signs ensure that the range $A$1:$A$100 remains fixed when the conditional formatting is applied to other cells.

Step 6: Set the Formatting Style

Click on the “Format…” button in the “New Formatting Rule” dialog. This will open the “Format Cells” dialog, where you can choose how you want to highlight the cells that meet the condition (i.e., values in the first table that are not in the second). You can change the fill color, font style, border, etc. For example, you might choose to fill the cell with a red color to easily spot the differences.

Step 7: Apply the Conditional Formatting

Click “OK” in both the “Format Cells” and “New Formatting Rule” dialog boxes. Now, the conditional formatting rule is applied. You may need to drag the formatting handle (the small square at the bottom right of the selected cell) to apply the rule to the entire range of your first table that you want to compare.

Alternative Formula using IFERROR and MATCH

For a more concise formula, you can use IFERROR with the MATCH function. The MATCH function returns the position of a value in a range, or an error if no match is found.

Here’s the alternative formula:

=IFERROR(MATCH(A1, Sheet2!$A$1:$A$100, 0), 0) = 0

Explanation:

MATCH(A1, Sheet2!$A$1:$A$100, 0): This attempts to find the position of A1 within the range Sheet2!$A$1:$A$100. 0 specifies an exact match.
IFERROR(..., 0): If MATCH finds a match, it returns a number (the position). If it doesn’t find a match, it returns an error (#N/A). IFERROR catches this error and returns 0 in case of an error.
... = 0: The formula then checks if the result of IFERROR is equal to 0. If it is 0, it means MATCH did not find a match (and IFERROR returned 0), so the condition is TRUE.

This formula achieves the same result as the VLOOKUP version but is slightly shorter.

Important Considerations for Table Comparison

Adjust Cell References and Ranges: Always ensure that the cell references (A1) and the table ranges (Sheet2!$A$1:$A$100) in the formulas are correctly adjusted to match the actual structure and location of your tables.
Reverse Comparison: If you need to identify values present in the second table but not in the first, you would repeat the process, but apply the conditional formatting to the second table and adjust the formula to reference the first table.
Complex Tables: For more complex tables with multiple columns to compare, you might need to adapt these formulas or consider using more advanced Excel features like Power Query for data comparison and merging.

By utilizing conditional formatting with these formulas, you can effectively compare two tables in Excel, quickly visualize data discrepancies, and gain valuable insights from your data.