How to Compare Two Excel Files

Don't waste time searching for minor differences

Two people looking at a spreadsheet

 Image Source/Getty Images

Knowing how to compare two Excel files is important in any environment where a lot of people are making changes to the same file. This is common in a business environment, or in the case where Excel files are shared on the cloud and many people have access to make changes.

Thankfully, there are a number of ways to compare two Excel files. In some cases you can even merge the changes made in two different Excel files into one single file.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, and Excel for Mac

How to Compare Two Excel Files

If you have two Excel files with multiple sheets, the easiest approach is to do a full worksheet comparison using third-party tools.

You can find a few tools online that help you compare Excel files, but there are also a few you can use for free. One of those is Spreadsheet Compare, available from SourceForge.

Download and run the app, which will install an add-on into your Excel application.

Spreadsheet Compare works on all version of Excel after Excel 2000. It's only compatible with the Windows operating system.

To use Spreadsheet Compare to compare two Excel files:

  1. Open both of the Excel files you want to compare, and select the Add-ins menu. In this menu, you'll see three options, Full Compare, Quick Compare, and Range Compare.

    Screenshot of the Spreadsheet Compare addon menu
  2. Select Full Compare. You'll see a pop-up window showing both files in two fields. If the file before changes were made isn't in the top field, just select the Swap button. Then select Next.

    Screenshot of the Spreadsheet Compare window
  3. In the next window, you can customize how the comparison performs. You can change where in the sheet the comparison starts, whether it's case sensitive, and how mismatches are identified. Select Next to move to the next window.

    Screenshot of customizing spreadsheet comparison
  4. Select the sheets you want to compare and select Add to move those sheets to the pane on the right. Select Next and do the same for the next sheet as well.

    Screenshot of adding sheets to compare
  5. Select Next to see the window with the report configuration settings. Modify them if you wish, then press Next twice and Compare to finish.

    Screenshot of the Report configuration settings
  6. Finally, you'll see each original sheet updated with the changed cells highlighted in red. The tool also creates a third sheet with a report showing the old value crossed out and the new value in its place.

    Screenshot of an Excel worksheet comparison report
  7. This tool is a powerful way to compare entire Excel worksheets and see quick results with all of the changes. You can scroll through and keep or remove the changes you want to keep.

Use Excel to Compare Two Sheets

If you have individual worksheets in an Excel file to compare, you can do this in a few different ways. One is to use formulas to create a third spreadsheet showing all of the differences. Another is through conditional formatting to highlight cells that have changed.

Compare Excel Spreadsheets Using Formulas

If you have two sheets that you want to compare, you can create a third sheet that'll highlight which cells are different and show you the difference. This is accomplished using an IF formula.

The IF formula is useful for comparing one cell at a time, and the using Excel's autofill feature to fill an entire results sheet with comparison results. It isn't useful for comparing entire workbooks without a lot of extra work.

  1. To start your comparison of two sheets, create a third sheet. Label it Results to make the results sheet easier to identify later.

    Screenshot of creating a Results sheet
  2. In Cell A2 of the results sheet, paste the following formula and press Enter:

    =IF(Sheet1!A2<>Sheet2!A2, "Sheet1:" & Sheet1!A2 & " And Sheet2:" & Sheet2!A2, "No Difference")

    You can modify this formula to start at any cell you like. If the data within your sheet starts at row B and column 3, you would change the formula to use B3 instead of A2.

    Screenshot of using the IF formula to compare cells.
  3. This formula compares the cell from Sheet1 to the same cell in Sheet2. If the cells are the same, the results sheet will display No Difference. If they're different, the cell will provide the different values from each sheet.

    Screenshot of cell comparison formula results
  4. Click the corner of the cell and drag the mouse across the sheet to the last column that has data from the other sheets you're comparing. This will fill the comparison formula to the last column and automatically adjust the cell references.

    Screenshot of filling all rows with the comparison formula
  5. With the same row highlighted, click the corner of the last cell and drag the mouse down the sheet to the last row that has data from the other sheets you're comparing. This will fill the comparison formula to the last row and automatically adjust the cell references.

    Screenshot of filling the results sheet with the comparison formula
  6. Scrolling through the sheet, you'll see all of the cells that are different between Sheet1 and Sheet2 are displayed with the values from each sheet. Just scroll through to identify all of the differences.

    Screenshot of worksheet comparison results
  7. Using formulas is one of the easiest ways to compare individual sheets, because you don't have to alter the original sheets in any way.

Compare Excel Sheets with Conditional Formatting

Another approach to comparing two sheets is using conditional formatting. As you can see from the results sheet above, with all cells using the same formatting, it can be difficult to spot the differences between sheets.

Using conditional formatting is especially useful for large sheets with a lot of data. Changing the color or formatting of cells that have differences makes it much easier to identify those differences even in sheets with many rows and columns of data.

You can use conditional formatting to identify the differences.

  1. First, highlight all cells in the results sheet. Select the Home menu, then select Conditional Formatting from the Styles group, and choose New Rule.

    Screenshot of selecting New Rule from Conditional Formatting menu
  2. In the New Formatting Rule window that appears, select Use a formula to determine which cells to format. In the Format values where this formula is true field, paste the following formula.

    =Sheet1!A2<>Sheet2!A2

    Just as with the formula approach, you can start the formatting feature at any cell. If your data starts at B3 instead of A2, edit this formula to use B3 instead. The formatting will start at B3 and fill all rows and columns below and to the right of it.

    Screenshot of conditional formatting comparison formula
  3. Select Format, and then configure the formatting you'd like the cells to have when there are differences between the two sheets. Then select OK.

    Screenshot of formatting cells for comparison differences
  4. Now you can see that where the cells between the two sheets have differences, the formatting is different.This makes those differences much easier to spot.

    Screenshot of conditional formatting showing cell differences

Compare Excel Worksheets Manually

One simple and quick way to compare two worksheets is by doing so visually. Excel provides an easy way to visually compare two sheets side-by-side.

  1. In the worksheet where you have two sheets you want to compare, select the View menu. Select New Window to open the same workbook in a new Excel window in the background.

    Screenshot of selecting New Window in Excel
  2. Select the View menu again, and click View Side by Side. This places the two workbook windows side by side, each filling half the screen.

    Screenshot of viewing Excel workbooks side by side
  3. In one window, select the other sheet you want to compare. As you scroll the sheet in the window on one side, you'll see the sheet in the other window scroll at the same time.

    Screenshot of side by side view in Excel
  4. Side-by-side viewing is especially useful in those situations where two worksheets are mostly the same, but you know there are a few differences. Synchronized scrolling lets you scan down the spreadsheet visually to find those differences.

Viewing Side-by-Side in Excel 2010 and Earlier

If you are using an earlier version of Excel, the individual worksheet files open in the same Window. You can still use the View Side-by-Side feature, but finding it is a little different.

  1. Open both files in the same Excel window.

  2. In the View menu, select Arrange All to split the open files into multiple sub-windows.

  3. Select the View Side by Side icon to view the sub-windows side by side and scroll both of them simultaneously.

Options Make It Easier to Compare Two Excel Files

The option you use to compare data in Excel depends on the volume of data and where its stored. If you have multiple sheets in two different Excel files, your best option is to use a third-party add-on like Spreadsheet Compare.

However, if you're only looking to compare two or more sheets inside one workbook, any of the other options work best. Use formulas and conditional formatting for large spreadsheets with just few small changes scattered throughout that you need to find. Use the visual side-by-side approach if you have two spreadsheets with lots of data changes that are easier to identify visually.