A Step-by-Step Guide to Compare Two Excel Files

PDF download Download Article
Compare two spreadsheets using synchronous scrolling, lookups, and more
PDF download Download Article

This article focuses on how to directly compare information between two different Excel files. Once you get to manipulating and comparing the information , you might want to use Look Up , Index, and Match to help your analysis.

Ways to Compare Excel Spreadsheets

  • Open both sheets to compare, then navigate to the “View” tab. Click “View Side by Side” to see both sheets in smaller windows.
  • Or, click on a cell in one of the sheets, then use the “Lookup & Reference” button under the “Insert” tab.
  • Alternatively, upload the sheets to https://www.xlcomparator.net/ to view and download a comparison document.
Method 1
Method 1 of 4:

Using Excel's View Side by Side Feature

PDF download Download Article
  1. Open the workbooks you need to compare. You can find these by opening Excel, clicking File then Open , and selecting two workbooks to compare from the menu that appears. [1]
    • Navigate to the folder where you have the Excel workbooks saved, select each workbook separately, and keep both workbooks open.
  2. Once you've opened one of the workbooks, you can click on the View tab in the top-center of the window.
    Advertisement
  3. This is located in the Window group of the ribbon for the View menu and has two sheets as its icon. This will pull up both worksheets into smaller windows stacked vertically. [2]
    • This option may not be readily visible under the View tab if you only have one workbook open in Excel.
    • If there are two workbooks open, then Excel will automatically choose these as the documents to view side by side.
  4. This setting lets you change the orientation of the workbooks when they're displayed side-by-side. [3]
    • In the menu that pops up, you can select to have the workbooks Horizontal , Vertical , Cascade , or Tiled .
  5. Once you have both worksheets open, click on Synchronous Scrolling (located under the View Side by Side option) to make it easier to scroll through both Excel files line-by-line to check for any differences in data manually. [4]
  6. Once Synchronous Scrolling is enabled, you'll be able to easily scroll through both workbooks at the same time and compare their data more easily.
    7. Advertisement
Method 2
Method 2 of 4:

Using the Lookup Function

PDF download Download Article
  1. You can find these by opening Excel, clicking File then Open , and selecting two workbooks to compare from the menu that appears.
    • Navigate to the folder where you have the Excel workbooks saved, select each workbook separately and keep both workbooks open.
  2. This is where a drop-down list will appear later.
  3. The border should darken.
  4. Once you've clicked on it, select VALIDATION in the drop-down menu. A pop up should appear.
    • If you're using an older version of Excel, the DATA toolbar will pop up once you select the DATA tab and display Data Validation as the option instead of Validation .
  6. This will let you pick your source (in other words, your first column), which will then be processed into data in the drop-down menu.
  7. Click OK when the data validation window appears. You should see a box with an arrow on it, which will drop-down when you click the arrow.
  9. In older versions of Excel, you can skip clicking the Insert tab and just click on the Functions tab to pull up the Lookup & Reference category.
  10. [5]
  11. When you double-click it, another box should appear and you can click OK .
  13. [6]
  14. [7]
  15. The info should automatically change.
    16. Advertisement
Method 3
Method 3 of 4:

Using XL Comparator

PDF download Download Article
  1. Open your browser and go to https://www.xlcomparator.net/ This will take you to XL Comparator's website, where you can upload two Excel workbooks for comparison.
  2. This will open a window where you can navigate to one of the two Excel documents you want to compare. Make sure to select a file for both fields.
  3. Once you've selected this, a pop-up message should appear at the top of the page letting you know that the file upload process has begun and that larger files will take longer to process. Click Ok to close this message.
  4. Underneath each file name is a drop-down menu that says Select a column . Click on the drop-down menu for each file to select the column you want to be highlighted for comparison.
    • Column names will be visible when you click the drop-down menu.
  5. There are four options with bubbles next to them in this category, one of which you'll need to select as the formatting guidelines for your result document.
  6. In the bottom cell of the comparison menu, you'll see two more conditions for your document comparison: Ignore uppercase/lowercase and Ignore "spaces" before and after values . Click the checkbox for both before proceeding.
  7. This will take you to the download page for your result document.
  8. Once you've uploaded your workbooks and set your parameters, you'll have a document showing comparisons between data in the two files available for download. Click the underlined Click here text in the Download the comparison file box.
    • If you want to run any other comparisons, click New comparison in the bottom-right corner of the page to restart the file upload process.
    9. Advertisement
Method 4
Method 4 of 4:

Accessing an Excel File Directly from a Cell

PDF download Download Article
    • In this case, we use three example workbooks located and named as follows:
      • C:\Compare\Book1.xls (containing a sheet named “Sales 1999”)
      • C:\Compare\Book2.xls (containing a sheet named “Sales 2000”)
    • Both workbooks have the first column “A” with the name of the product, and the second column “B” with the amount sold each year. The first row is the name of the column.
  2. We will work on Book3.xls to do a comparison and create one column containing the products, and one with the difference of these products between both years. [8]
    • C:\Compare\Book3.xls (containing a sheet named “Comparison”)
  3. Only with “Book3.xls” opened, go to cell “A1” and type:
    • ='C:\Compare\[Book1.xls]Sales 1999'!A1
    • If you are using a different location replace “C:\Compare\” with that location. If you are using a different filename remove “Book1.xls” and add your filename instead. If you are using a different sheet name replace “Sales 1999” with the name of your sheet. Beware not to have the file you are referring (“Book1.xls”) opened: Excel may change the reference you are adding if you have it open. You’ll end up with a cell that has the same content as the cell you referred to.
  4. 4
    Drag down cell “A1” to list all products. Grab it from the bottom right square and drag it, copying all names.
  5. In this case, we call it “Difference” in "B1".
  6. In this case, by typing in cell “B2”:
    • ='C:\Compare\[Book2.xls]Sales 2000'!B2-'C:\Compare\[Book1.xls]Sales 1999'!B2
    • You can do any normal Excel operation with the referred cell from the referred file.
    8. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Remember it is important to have the referred files closed. If you have them opened, Excel may override what you type in the cell, making it impossible to access the file afterward (unless again you have it open).
        Thanks
        Helpful 0 Not Helpful 0


      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Add the Google Play Store to an Amazon Fire Tablet
      How to
      Play Minesweeper
      How to
      Open EPUB Files
      Using Rufus to Create Bootable USB Drives: A Step-By-Step Guide
      How to
      Save an Animation in Blender
      How to
      Make an Exe File
      2 Simple Ways to Create an RSS Feed
      How to
      Add a Password to a RAR File
      How to
      Turn on Microsoft Defender in Windows 10 and 11
      How to
      Change the Hostname on Linux Without Rebooting
      How to
      Edit DLL Files
      How to
      Crack Software by Modifying DLL Files
      2 Easy Ways Connect the Kindle Fire to a Computer
      How to
      Run a .Jar Java File
      Advertisement

      References

      1. ↑ https://edu.gcfglobal.org/en/excel2016/creating-and-opening-workbooks/1/
      2. ↑ https://support.microsoft.com/en-us/office/compare-two-versions-of-a-workbook-by-using-spreadsheet-compare-0e1627fd-ce14-4c33-9ab1-8ea82c6a5a7e
      3. ↑ https://support.microsoft.com/en-us/office/compare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab
      4. ↑ https://support.microsoft.com/en-us/office/compare-two-or-more-worksheets-at-the-same-time-1deed3da-a297-4260-98aa-a7b2d90c81ab
      5. ↑ https://kb.wisc.edu/msndata/page.php?id=90851
      6. ↑ https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb
      7. ↑ https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb
      8. ↑ https://support.microsoft.com/en-us/office/insert-the-current-excel-file-name-path-or-worksheet-in-a-cell-186833c6-c899-4912-a14c-240c2eb51e0b

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 353,848 times.

      Is this article up to date?

      Advertisement