How to Compare Two Excel Files

Being able to compare two Excel files enables you to spot differences with ease. Learn how to compare two Excel workbooks with ease.

Learn how to compare two workbooks with ease. Being able to compare two Excel files enables you to spot differences with ease. This is especially useful in environments where multiple people work on the same file, such as businesses with cloud sharing.

Compare Two Excel Files

 

There are a number of ways to get this done, some of which even allow you to merge two documents into one.

Please note that our instructions apply to Excel 2019, 2016, 2013, 2010; Excel for Microsoft 365, and Excel for Mac. If you have a different version of Excel, some steps may need to be performed differently.

How to compare two Excel files

If you want to compare two entire workbooks that consist of multiple pages, your best bet is using a third-party tool. Currently, Excel doesn’t provide an easy way to compare two workbooks, meaning you must download another developer’s software to accomplish this task.

We recommend using Spreadsheet Compare from SourceForge, which is a free software for Windows. All you have to do is download the application and install it — it’ll automatically show up as an Excel add-on the next time you run the software.

Here’s how to use Spreadsheet Compare to compare two Excel workbooks:

  1. Open both of the Excel workbooks you want to compare. Please note that you can only compare two files at once.
    Compare two excel files
  2. Click on the Add-ins menu. You should see three options: Full Compare, Quick Compare, or Range Compare. For the purposes of this tutorial, we’ll choose Full Compare.
    compare two excel files
  3. A pop-up window should appear on your screen, showing both of the files next to each other. Click the Swap button if one of the files doesn’t show up correctly. Click Next when you’re done.
    compare two excel files
  4. The next window allows you to customize how you want to compare your files. You can edit the range, choose whether or not your comparison is case sensitive, and how mismatches should be identified. Click Next to proceed.
    compare two excel files
  5. Select which sheets you want to compare in your workbooks. Clicking the Add button moves those sheets to the pane on the right. Click on the Next button to select the sheets for the next pane as well.
  6. In the following window, you can modify the report configuration settings. Continue pressing Next until you reach the end of the wizard, then press Compare to finish.
    compare two excel files
  7. You’ll see the original sheet updated with the differences highlighted in red, along with a report to show all changes.

How to compare two Excel sheets

Comparing two sheets within the same Excel workbook is an easier task to accomplish. All you have to do is use an IF formula to create a third sheet which highlights all the differences in the other two sheets.

  1. Get started by creating a third sheet in the same workbook. Label it Results to make it easier to identify later on.
  2. Switch to the results sheet and select cell A2. Paste the following formula in the cell and press Enter
    =IF(Sheet1!A2<>Sheet2!A2, "Sheet1:" & Sheet1!A2 & " And Sheet2:" & Sheet2!A2, "No Difference")
  3. Modify the formula as needed. For example, if the data you want to compare starts at row C and column 5, you need to change the formula to use C5 instead of A2.
    Compare two excel sheets
  4. This IF formula works by comparing two cells from Sheet1 and Sheet2. If both of the cells have the same contents, the formula will return “No Difference”, however, if the contents don’t match, the different values will be displayed.
    compare two excel sheets
  5. Click on the corner of the A2 cell in the Results sheet, then drag your cursor across the sheet until you reach the last cell which has data in it on your other sheets. This is going to fill each cell with the comparison formula and adjust the cell references. Do this both horizontally and vertically.
    compare two excel sheets
  6. The sheet should be filled with all of the information pulled from Sheet1 and Sheet2. Just scroll through the Results sheet to spot the differences and compare your other sheets.
    compare two excel sheets
  7. This method allows you to compare two sheets without having to alter the original ones in any way.

Final thoughts

If you need any further help, don’t be afraid to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern day technology!

Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.

(Image sources: Lifewire, Microsoft)

Also Read

How to Change Series Name in Excel
7 Tips to Become an Excel Mastermind
Excel: How to use NPER Function in Excel