Power Query: Compare two tables in Excel

Comparing two tables in Excel is a frequent requirement when working with data from different sources, updated datasets, or to perform data audits. Common goals include identifying records that are present in both tables, pinpointing differences, or specifying which entries are missing from either list. For example, you may receive new data from a colleague or an external system and need to compare it with your master list for updates or discrepancies. In the screenshots below, two tables with identical column structure contain some different and some matching records. The ability to efficiently highlight these differences or similarities is crucial for data validation and business decision-making.

This tutorial provides a detailed explanation of comparing two tables using the Power Query feature. Additionally, if you are seeking alternative and practical methods—including by formula, VBA code or Conditional Formatting—please refer to the solutions added in the Table of Contents below.

Compare two tables in Power Query

Alternative solutions

• Excel Formula - Compare two tables with formula
• VBA Code - Compare two tables with VBA code
• Conditional Formatting - Visually compare tables

Compare two tables in Power Query

Power Query is an effective tool in Excel for restructuring and analyzing data from multiple sources. When you need to compare two tables of data row by row, Power Query automates much of the process, reducing manual comparison errors and saving time. Please follow the step-by-step guide below to compare two tables, with notes and practical tips included to ensure a smooth workflow.

Creating queries from the two tables

1. Select the first table that you want to compare. In Excel 2019 and Excel 365, go to the Data tab, and click From Table/Range. See screenshot below.
Tip: Make sure your table is formatted as an actual Excel Table (Ctrl+T) before starting. This helps Power Query recognize the data boundaries accurately.

Note: In Excel 2016 and Excel 2021, the menu reads Data > From Table. They are equivalent in function.
If your selection is not formatted as a Table, Excel may prompt you to create one.

2. The Power Query Editor window opens. Here, you can review or clean your data if needed, but for comparison, you can proceed directly. Click Close & Load > Close & Load To to set connection options.

3. In the Import Data dialog box, choose Only Create Connection, then click OK. This option lets you use the data only inside Power Query without immediately loading it back into the worksheet. See the following screenshot.

4. Repeat the previous steps (1–3) to create a connection for your second table. Now, both tables appear as separate connections in the Queries & Connections pane. This prepares your data for the comparison step.
Tip: Double-check that both tables have identical column names and structure to ensure an accurate comparison in the next step.

Joining queries to compare two tables

With both queries created, you now merge them to compare and reveal differences or matches line by line.

5. In Excel 2019 and Excel 365, select the Data tab, then click Get Data > Combine Queries > Merge. This launches the merge process. See screenshot.

Note: For Excel 2016 and Excel 2021, access this via Data > New Query > Combine Queries > Merge—the process remains the same.

6. In the Merge dialog box:

• Select the first and second table queries in the two dropdowns.
• Select the columns you want to compare in each table—press Ctrl to choose multiple columns. Usually, all columns must be selected for a true row-wise comparison.
• Choose Full Outer (all rows from both) as the Join Kind. This option matches all rows and highlights missing, extra, or differing records.
• Click OK to continue.

7. A new column appears for the matched data from the second table:

• Click the small Expand button (two arrows) next to the new column header.
• Select Expand, and choose which columns to include in the results (generally, all columns).
• Press OK to insert them.

8. The second table’s data is now shown beside the first table’s, making it simple to compare records. To return this merged data to Excel, go to Home > Close & Load > Close & Load. This will add the side-by-side comparison to a new worksheet.

9. In the resulting worksheet, you can readily spot matches and mismatches: identical rows appear side-by-side, while differences stand out as blank or differing cells. This layout enables you to efficiently locate unique, missing, or changed records between the two tables.
Troubleshooting tip: If some records do not match as expected, recheck that the join columns have consistent formats and that extra spaces or typos are not present in your source data. Power Query is sensitive to even minor differences.

In summary, Power Query simplifies the complex process of comparing two tables in Excel. Once you familiarize yourself with these steps, you can extend the process to more elaborate data scenarios, such as comparing tables from different files or performing multi-field matches. When working with frequent data updates, saving the Power Query steps as a template can speed up future comparisons with similar structures. If you encounter difficulties with column selection or merging, double check column data types, and consider removing any duplicate rows in advance to avoid matching issues.

If Power Query feels too advanced for occasional tasks, or you need to highlight differences directly in the worksheet or automate repetitive comparisons, you can also use the alternative solutions described below.

Excel Formula - Compare two tables with formula

Compare two tables row by row to check for differences,one powerful way to perform this check is by using Excel’s TEXTJOIN function in combination with an IF formula.

Suppose you have Table1 in cells A2:C10 and Table2 in cells F1:H10, and you'd like to check which items in Table1 do not appear in Table2.

1. Enter the following formula in cell I2:

=IF(TEXTJOIN("|",,A2:C2)=TEXTJOIN("|",,F2:H2), "Match", "Mismatch")

2. Then, drag the formula to other cells to get the result.If both rows in each table are exactly the same, the formula returns "Match"; otherwise, it returns "Mismatch".

VBA Code - Compare two tables with macro automation

VBA approach is ideal when you want to quickly and visually compare two tables row by row without cluttering your worksheet with formulas. It's especially useful for auditing or verifying imported data in a clean and user-friendly way.

1. Click Developer Tools > Visual Basic to open the VBA editor.

2. In the editor, click Insert > Module and paste the following code into the module window:

Sub CompareSelectedTablesRowByRow()
    Dim rng1 As Range, rng2 As Range
    Dim rowCount As Long, colCount As Long
    Dim r As Long, c As Long
    Dim xTitle As String
    xTitle = "Compare Tables - KutoolsforExcel"
    On Error Resume Next
    Set rng1 = Application.InputBox("Select the first table range:", xTitle, Type:=8)
    If rng1 Is Nothing Then Exit Sub
    Set rng2 = Application.InputBox("Select the second table range:", xTitle, Type:=8)
    If rng2 Is Nothing Then Exit Sub
    On Error GoTo 0
    If rng1.Rows.Count <> rng2.Rows.Count Or rng1.Columns.Count <> rng2.Columns.Count Then
        MsgBox "Selected ranges do not have the same size.", vbExclamation, xTitle
        Exit Sub
    End If
    rng1.Interior.ColorIndex = xlNone
    rng2.Interior.ColorIndex = xlNone
    For r = 1 To rng1.Rows.Count
        For c = 1 To rng1.Columns.Count
            If rng1.Cells(r, c).Value <> rng2.Cells(r, c).Value Then
                rng1.Cells(r, c).Interior.Color = vbYellow
                rng2.Cells(r, c).Interior.Color = vbYellow
            End If
        Next c
    Next r
    MsgBox "Comparison complete. Differences are highlighted in yellow.", vbInformation, xTitle
End Sub

3. To run the code, select the Run button in the VBA window, or press F5. Select the first table range, then the second table range when prompted. The macro will check each cell row by row of the two tables, if the values differ, cells in both tables are highlighted in yellow.

Conditional Formatting - Visually compare tables

Conditional Formatting offers a visually intuitive method to highlight differences or matches directly within your worksheet. This method is suitable when you need to identify duplicate or distinct values without altering the underlying data, and it works particularly well with smaller tables displayed side by side.

1. Select your first table range (e.g., A1:C10).
2. Go to Home > Conditional Formatting > New Rule.
3. Click Use a formula to determine which cells to format and enter this formula：=A2<>F2
4. Click Format, select a fill color, and click OK > OK to apply the rule.

Result: The highlighted cells represent values in Table1 that do not exist in Table2. You may repeat the process for Table2 vs Table1 as needed.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!