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.
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 that you must download another developer’s software to get this task accomplished.
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:
- Open both of the Excel workbooks you want to compare. Please note that you can only compare two files at once.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Get started by creating a third sheet in the same workbook. Label it Results to make it easier to identify later on.
- 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")
- 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.
- 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.
- 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.
- 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.
- This method allows you to compare two sheets without having to alter the original ones in any way.
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)