How to Compare Two Excel Files

Brad Jones 27-12-2017

Need to compare two Microsoft Excel files? Here are two easy ways to do so.


There are plenty of reasons why you might need to take one Excel document and compare it to another. This can be a time-consuming task that requires a lot of concentration, but there are ways to make it easier on yourself.

Whether you just need to take a close look manually, or you want Excel to do some of the heavy lifting 3 Crazy Excel Formulas That Do Amazing Things Excel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel. Read More on your behalf, here are two straightforward methods to compare multiple sheets.

How to Compare Excel Files

Excel makes it easy for users to put two versions of a document on the screen at once, in order to quickly establish the differences between them:

  1. First, open the workbooks that you need to compare.
  2. Navigate to View > Window > View Side by Side.

Comparing Excel Files by Eye

To get started, open Excel and any workbooks that you’re looking to compare. We can use the same technique to compare sheets that are in the same document 3 Beginner Tips for Managing Worksheets in Excel Worksheets are the core essence of Excel. Here are a few tips that'll help you stay organized and productive. Read More or entirely different files.

If more than one sheet comes from the same workbook, you’ll need to separate it beforehand. To do so, navigate to View > Window > New Window.


how to compare two excel sheets side by side

This won’t separate the individual sheets permanently, it simply opens up a new instance of your document.

Next, head to the View tab and find View Side by Side in the Window section.

how to compare two excel sheets side by side


This menu will list all the spreadsheets that are currently open. If you only have two open, they will be selected automatically.

how to compare two excel sheets side by side

Make your selection and click OK. You’ll see both spreadsheets appear on the screen.

how to compare two excel sheets side by side


If it’s more convenient, you can use the Arrange All button to switch between a vertical and a horizontal configuration.

how to compare two excel sheets side by side

One important option to be aware of is the Synchronous Scrolling toggle.

how to compare two excel sheets side by side


Turning this on will ensure that when you scroll one window, the other will move in sync. This is essential if you’re working with a large spreadsheet How to Split a Huge CSV Excel Spreadsheet Into Separate Files One of Microsoft Excel's shortcomings is the limited size of a spreadsheet. If you need to make your Excel file smaller or split a large CSV file, read on! Read More and you want to continue to check one against the other. If the two sheets become unaligned for any reason, simply click Reset Window Position.

Comparing Excel Files Using Conditional Formatting

In many cases, the best way of comparing two spreadsheets might simply be to put both of them on-screen at once. However, it is possible to automate the process somewhat.

Using conditional formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More , we can make Excel check for any discrepancies between two sheets. This can save a lot of time if all you need to find are the differences between one version and another.

For this method, we’ll need to make sure that the two sheets we’re working with are part of the same workbook. To do so, right-click the name of the sheet you want to transfer and select Move or Copy.

how to compare two excel sheets side by side

Here, you can use the dropdown menu to decide which document it will be inserted into.

how to compare two excel sheets side by side

Select all the cells that are populated in the sheet where you want any differences to be highlighted. A quick way to do this is by clicking the cell in the top-left hand corner and then using the shortcut 3 Essential Time-Saving Excel Shortcuts You Should Know Excel can be a headache for the uninitiated, but with these shortcuts in hand, you'll save yourself a ton of time going forward! Read More Ctrl + Shift + End.

Navigate to Home > Styles > Conditional Formatting > New Rule.

how to compare two excel sheets side by side

Select Use a formula to determine which cells to format and enter the following:


Just remember to sub out “sheet_name” for whatever the name of the other sheet is. All this formula is doing is checking when a cell in one sheet doesn’t exactly match the corresponding cell in the other sheet, and flagging each instance.

Next, click Format and choose how you want to highlight any discrepancies. I’ve gone for a standard red fill. Next, click OK.

how to compare two excel sheets side by side

Above, you can see the results. Any cells that contain a change have been highlighted red, making it quick and easy to compare the two sheets.

Let Excel Do the Hard Work

The technique above demonstrates one way that you can let Excel handle some grunt work. Even if you’re paying close attention, there’s a chance you might miss a change if you were to perform that task manually. Thanks to conditional formatting, you can ensure that nothing slips through the net.

Excel is good at monotonous and detail-oriented jobs. Once you have a grasp of its capabilities, you can often save yourself some time and effort by using a technique like conditional formatting and a bit of ingenuity.

When you need to do more than compare documents, but find specific values, you should get familiar with Excel’s vlookup function 4 Excel Lookup Functions to Search Spreadsheets Efficiently Searching a large Excel spreadsheet isn't always easy. Use lookup formulas to save time and search spreadsheets efficiently. Read More . For a different way to do this, try comparing files with Notepad++ How to Make Notepad++ Compare Two Files With a Plugin Notepad++ is jampacked with features. The Compare plugin is another great add-on if you want to compare two files easily. Read More instead or check out these Mac file comparison tools.

Related topics: Microsoft Excel, Microsoft Office Tips, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Evan
    July 9, 2019 at 5:29 pm

    This formula has been a lifesaver in the office. Small correction though:


    The sheet name has to be within '' or else you get the following error, "you may not use Reference operators (such as unions, intersections, or ranges) or array constants for Conditional Formatting criteria."

  2. DS
    June 11, 2018 at 6:11 pm

    I followed the instructions completely but when I hit OK I get an error pop-up msg stating: "You may not use Reference operators (such as unions, intersections, or ranges) or array constants for Conditional Formatting criteria"

    Not sure what's going wrong?

  3. JaS
    January 1, 2018 at 11:16 pm

    The eXcel files are "Zipped/XML" files. Change the extension to ".zip" and open it. You will find several XML files describing the spreadsheet. One of them has a detailed entry for each cell. Text compare this with the other eXcel file you changed to a zip.

  4. Andrew
    December 30, 2017 at 2:53 pm

    to compare table of numerical values, edit / copy one. then at the top left of the other, use paste special, difference
    Any place where the two values in the two tables are the same will then show as zero, non zero shows where they are different.

    NOTE - this method is destructive (sort of) - it overwrites the table where you paste, but you can always use undo to reversde that

  5. Flo
    December 28, 2017 at 4:53 pm

    Nice.. I like the Cond. Formatting option.

  6. Phú Ngô
    December 28, 2017 at 2:49 pm

    Another way for compare number
    + Put 2 data compare on 1 sheet, by above and below.
    + Add 1 column beside it with detail for indentify source 1 and source 2, title is source
    + Revert value want to compare of source 1
    + using pivot table
    We see result is column total, total is 0 duplicate, total not 0 is difference.