Excel compare two tables and return differences

In this guide, we're going to show you how to compare two tables using Get & Transform Data in Excel.

Download Workbook

In our example, there are two identical tables with the same column names. Although both tables contain the same data under the Name column, there are differences in the data under the Form column.

For example, both tables contain “Charizard”, while the left table has “Mega X” and the right one has “Mega Y”.

We can join these two tables to compare them by using Get & Transform Data. Get & Transform Data feature consists of tools previously known as Power Query. You can get more information about this here: Power Query 101.

Creating queries from tables

First step is to convert the tables into Power Query structure.

1. Select one your tables.
2. Open the Data tab and click the corresponding icon in Get & Transform Data section to get data from your workbook.
   1. (Microsoft 365 / Excel 2019) From Sheet
      
      
      
      
   2. (Excel 2016) From Table/Range
   3. (Excel 2010 / Excel 2013) Use Power Pivot tab instead of Data
3. Clicking the button opens the Power Query Editor. You can do any preliminary work on your data in this window. For example, you can filter the data, remove or merge columns without modifying the actual data, and so on.
4. Once you are done with your changes, click Close & Load To under the Close & Load list to see other options.
   
   
5. Make sure to select Only Create Connection under Import Data. This option will save the table as a connection rather that populating the queried data inside the worksheet.
   
   
6. Apply the same steps 1-5 to the other table. At the end you should have two queries corresponding with your tables. You can see these tables in the Queries & Connections pane.
   
   

Joining queries to compare two tables using Get & Transform Data

Next step is to join (merge) the queries in a new table. This will allow us to see the differences between tables.

1. Once the queries from the tables are ready, follow Data > Get Data > Combine Queries > Merge to open the Merge dialog of Power Query.
   
   
2. Select each table in the drop downs.
3. Select the columns with different data. For example, if we do not select both Form columns, we will see each Charizard item on both tables in the same row.
4. Finally select the Full Outer option for Join Kind to join all rows from both tables.
5. Click OK to apply.
   
   
6. You will see a new column for the second table next to the first table. Click the button next to the column title.
7. Make sure that the Expand option is selected.
8. Uncheck any columns you do not want to insert into the comparison table.
9. Click OK to populate the data of the second table.
   
   
10. Once you are satisfied, click Close & Load to populate the entire data into your worksheet.
    
    

After populating the merged table, you can easily find any rows containing different data. The rows that contain the same data in the specified columns are listed in the same column. Empty columns indicate rows with different data.