4 Easy Ways You Can Compare Two Excel Files

There are many reasons why you may need to compare Excel files, such as ensuring data integrity and consistency and data analysis. This can be quite difficult if you have to keep switching between windows, especially when dealing with large datasets or collaborating with multiple stakeholders.

Excel offers a myriad of possibilities for comparing two Excel files and identifying the differences between them.

In this guide, we will walk you through step-by-step instructions for how to compare two Excel files. Read on to learn more.

How to Compare Two Excel Files for Differences

There are various methods you can use to compare files in Excel for differences. These include:

1. Using View side by side
2. Using formulas
3. Using Spreadsheet Compare Add-in
4. Using Third-party Tools

For this tutorial, we’ll be using the example workbooks below:

Let’s look at these methods.

Compare Two Excel Files For Differences Using View Side By Side

Unlike Google Sheets, Excel has a built-in feature called view side by side that lets you do just that; view Excel files side by side.

Here’s how to compare two Excel spreadsheets using view side by side:

1. Go to Excel and open all the workbooks you want to compare. You can do this by going to File > Open and then selecting the workbooks.
2. Go to the View tab and select View side by side.

Your Excel window will be split into 2 horizontally so that you can view the two windows together.

If you want the windows to split vertically instead, then you can click on the option for Arrange all.

Then choose the vertical option in the pop-up.

There are more options to make this feature work seamlessly, for example:

1. Simultaneous scroll- This lets you scroll up and down both windows at the same time.
2. Freeze pane- This lets you freeze the two rows so that they don’t move while you scroll.
3. Reset Window Position- This lets you change the comparison orientation back to landscape format.

Compare Excel Files For Differences Using Formulas

IF is a powerful function that has many uses, and one of them is to compare files. When using formulas to compare Excel files, you’ll need to open a blank new document that will be used to transfer all the differences.

To compare Excel files for differences using the IF formula, follow these steps:

1. Open both worksheets and enable view side by side
2. Open a New sheet in one of the workbooks by clicking the + sign at the bottom.

3. In the new sheet, enter the following:

=IF(Sheet1!A3=[Book1.xlsx]Sheet1!$A$3,Sheet1!A3,"Sheet1:"&Sheet1!A3&"|Sheet2:"&[Book1.xlsx]Sheet1!A3)

You may need to adjust this formula based on the cell references, sheet names and file names of your workbooks.

4. Copy the IF formula down the column by dragging down the small square on the bottom right of the cell to cover all the rows you want to compare.

This formula works by returning the values from both sheets in separate lines in the same cell.

The limitations to using this formula are that you can only compare two sheets, and you can only compare the values in those sheets. You can also use the VLOOKUP to compare different Excel sheets or files.

Compare Excel Files For Differences Using Spreadsheet Compare Add-in:

There are plenty of add-ins in Excel that help make your life easier. One such tool is the Spreadsheet Compare add-in.

This add-in lets you easily compare Excel files by analyzing them and giving you a summary of the differences in the files.

Here’s how to use it:

1. First, turn on the Inquire tab. Go to File> Options and select Add-ins in the sidebar.

2. Go to the drop-down for Manage and choose COM Add-in and click Go.

3. In the new prompt, check the box for Inquire and click Ok.

4. Open the two Excel files you want to compare.
5. Go to the Inquire tab, and select Compare Files.

6. Your files will automatically appear. Click Swap files, then click Compare.
7. The Spreadsheet Compare window will open, showing an analysis of the comparison between the 2 files.

Keep in mind that Spreadsheet Compare is only available for certain versions of Microsoft Office, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for Enterprise.

Compare Excel Files For Differences Using Third-Party Tools

Third-party tools can be pretty useful and convenient tools if you find the right ones. There are a number of third-party tools whose function is to compare Excel files. If you don’t have any of the Microsoft versions mentioned, then you can use an online tool like  xlcomparator.net

1. Open xlcomparator.net
2. Click Choose Files to upload your 2 Excel files
3. Click on the Next button.

4. Select the columns you want to compare using the file columns drop-down.

5. Select one of the four options, whether you want to get matching data or different data.
6. Click on Next
7. Download the comparison file.

The comparison file will show you the difference between the Excel files.

Note that we recommend using an online tool as opposed to the software you need to download since it may not be safe to use and can harm your device.

How to Compare Two Excel Sheets And Highlight the Differences

To make it easier for you to see the differences between your 2 Excel files, you can also use conditional formatting to highlight the differences in one of the worksheets:

1. Open your two Excel worksheets.
2. Go to the spreadsheet where you want the differences to be shown and select the data range.

3. On the Home tab, click the Conditional Formatting drop-down.

4. Select New rule.

5. Choose the option “ Use a formula to determine which cells to format” and enter the formula below in the text box

=A1<>Sheet2!A1

Where Sheet 2 is the name of the other sheet you are comparing.

6. Click on Format

7. In the new window, go to Fill and choose the color you want to use to highlight the differences.

8. Once you’re done, click OK.

As a result, the cells with different values will be highlighted in the cell you put the conditional formatting in while the cells that are similar will remain the same. This will help you easily spot the differences when viewing the two Exel files.

How to Compare and Merge Copies of a Shared Workbook

Excel also has a feature that lets you compare and merge copies of a shared workbook. Before you enable this feature, make sure that you save your changes. Here’s how you can do it:

1. Go to File > Options

2. Select the Quick Access toolbar.
3. Go to the drop-down for Choose commands from and select the All commands option.

4. Look for the Compare and Merge Workbooks command in the list for commands and click Add.

5. Click OK.

The icon for compare and Merge will appear in the Quick Access toolbar.

6. Click the Compare and Merge Workbooks icon.
7. You may be asked to save the workbook if you hadn’t done so.
8. A window will pop up to open files. Select the copies of the workbook to merge into the open copy or originally shared workbook.
9. Click Ok.

Your changes, as well as other users’ changes, will be combined which will show the cells with changes in different highlighted colors.

Related: convert Excel to Google Sheets

How to Compare 2 Different Sheets in the Same Excel Workbook

One way you can compare sheets in Excel is by using the formula method. Previously we showed you how to use the IF formula to compare two Excel files. Now we’ll show you how to use it to compare two sheets in the same workbook:

1. Open a new sheet by clicking the + sign at the bottom.
2. In the new sheet, select a cell where you want to start the comparison.
3. Enter the following formula:

=IF(Sheet1!A3=Sheet2!A3,Sheet2!A3,"Sheet1:"&Sheet1!A3&"|Sheet2:"&Sheet1!A3)

Make sure to replace “Sheet1” and “Sheet2” with the actual names of the sheets you want to compare. You can also replace “A3” with the cell reference that represents the first cell you want to compare.

4. Copy the formula to the rest of the cells.

The comparison results will be displayed in the cells, indicating whether the values in the two sheets match or differ. Where the values don’t match, you will get the actual value from the first sheet and the second sheet in two separate columns, but if they do match, you get one column with the common value.

Frequently Asked Questions

Can You Compare Two Excel Files for Differences?

Yes, you can help you compare two Excel files for differences. There are a few different methods you can use to accomplish this task. We’ve shown you different methods like using the view side-by-side option, using formulas, and using third-party tools.

Can You Use VLOOKUP to Compare Two Excel Spreadsheets?

Yes, you can use the VLOOKUP function in Excel to compare two Excel spreadsheets. The VLOOKUP function is commonly used for searching and retrieving data from a specific column in a table. You can combine the Vlookup function with another function, like the IF function, to find differences in your Excel files, but a more straightforward method would be to use the IF formula as we have shown you above.

Bottom Line

You don’t need to stare cross-eyed at different worksheets as you switch between windows anymore. With this tutorial, we’ve shown you how to compare two Excel files using several different methods.

If you found this guide useful, you can also check out our premium templates, you’ll be sure to find something to suit your needs. Just remember to use the code SSP to save 50% at checkout.

Get Premium Templates

Related:

• How to Convert Google Sheets to Excel
• How to Use HLOOKUP in Excel
• How to Remove Rows in Microsoft Excel
• The Ultimate Guide to Excel Version History
• How to Track Changes in Excel
• Master the Excel Consolidate Features
• A Simple Guide on How to Lock Cells in Excel
• How to Remove Read Only From Excel
• How To Calculate Age in Excel [12 Easy Examples]