Excel: Comparing Two Tables - How to Find the Differences

With Excel, you have the ability to create tables that you can then use to calculate different values. If there are two tables that are quite similar, but you want to find out their exact differences, you can do that with conditional formatting.

How to compare two tables in Excel

The best way to compare two tables in Excel is to use conditional formatting. With this you can define rules that are then applied to a specific workbook (Excel file).

1. If both tables are in one file, split them into two files by selecting one completely and cutting it out with Ctrl + X. The first file is the Excel workbook. Now click on the plus symbol at the bottom next to the name of the file and create a new workbook.
2. Now paste the table with Ctrl + V exactly at the same place where the other table is located in the first file.
3. Go back to the first file and select the complete table by pressing the small green triangle in the upper left corner between 1 and A.
4. Now go to "Start" on "Conditional Formatting" and select "New Rule" > "Formula to determine the formatted cells".
5. Enter under "Format values for which this formula is true " =A1<>Table2!A1" and select under "Format" a color with which the deviating cells should be colored.
6. Confirm the process with "Ok". Now all deviations should appear correctly colored.