4 Simple Ways to Compare Two Different Excel Spreadsheets

PDF download Download Article
Compare XLS files on your computer
PDF download Download Article

Do you need to compare two Excel sheets to find the differences? If you have the Professional or enterprise version of Excel, you can use the Spreadsheet Compare app. If you have another version of Excel, you can still compare two files using built-in tools, such as Side by Side View. Here's how to compare two Excel files using synchronous scrolling, lookups, and more.

Comparing Spreadsheets in Excel

To compare two open workbooks in Excel, you can use Spreadsheet Compare, which is available on Office Professional and enterprise. If you don't have this, open both workbooks, click the View tab, and then click View Side By Side .

Method 1
Method 1 of 4:

Using Spreadsheet Compare

PDF download Download Article
  1. 1
    Open Spreadsheet Compare. You can find this program in the Start menu. If you don't see it, type it into the menu.
    • Spreadsheet Compare is only available on Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. [1]
    • If you have the regular version of Excel, skip to the next method instead.
  2. 2
    Click Home . You can find this tab at the top.
    Advertisement
  3. 3
    Click Compare Files . This is on the Home tab.
  4. 4
    Click the blue folder icon and select your first Excel file. You'll need to browse your device for the file. Alternatively, you can enter a web address if you have a website where your files are saved.
  5. 5
    Click the green folder icon and select the second Excel file. You'll need to browse your device for the file. Alternatively, you can enter a web address if you have a website where your files are saved.
  6. 6
    Check Select All . This is what you'll see in the workbook comparison. If you don't want to see everything, check the box next to the option(s) you want to see.
  7. 7
    Click OK . This will run the comparison. You'll see both files in a two-pane grid.
    8. Advertisement
Method 2
Method 2 of 4:

Using the Side by Side Feature

PDF download Download Article
  1. 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. [2]
    • Navigate to the folder where you have the Excel workbooks saved, select each workbook separately, and keep both workbooks open. You should have two Excel tabs open.
  2. 2
    Click the View tab. Once you've opened one of the workbooks, you can click on the View tab in the top-center of the window.
  3. 3
    Click View Side by Side . You can find this in the Window section. This will pull up both worksheets into smaller windows stacked vertically.
    • 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. 4
    Click Arrange All . This setting lets you change the orientation of the workbooks when they're displayed side-by-side.
    • In the menu that pops up, you can select to have the workbooks Horizontal , Vertical , Cascade , or Tiled .
  5. 5
    Enable Synchronous Scrolling. Once you have both worksheets open, click on the Synchronous Scrolling icon below the View Side By Side icon.
    • This makes it easier to scroll through both Excel files line-by-line to check for any differences in data manually.
  6. 6
    Scroll through one workbook to scroll through both. 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 3
Method 3 of 4:

Using the Lookup Function

PDF download Download Article
  1. 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.
    • Navigate to the folder where you have the Excel workbooks saved, select each workbook separately and keep both workbooks open.
  2. 2
    Decide on which cell you would like the user to select from. This is where a drop-down list will appear later.
  3. 3
    Click on the cell. The border should darken.
  4. 4
    Click the Data tab. This is in the top toolbar.
  5. 5
    Click Data Validation . If a drop-down menu opens, click Data Validation again.
  6. 6
    Click the "Allow" drop-down menu and select List . This is on the Settings tab.
  7. 7
    Click the button with the red arrow. 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.
  8. 8
    Select the first column of your list and press ↵ Enter . 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. 9
    Select the cell where you want the other info to show up. This can be any blank cell.
  10. 10
    Click the Formulas tab. This is at the top.
  11. 11
    Click Lookup & Reference . This is near the blue book with a magnifying glass.
  12. 12
    Find Lookup in the list. When you click it, another box should appear and you can click OK .
  13. 13
    Set your lookup values. Select the cell with the drop-down list for the Lookup_value . Select the first column of your list for the Lookup_vector . Select the second column of your list for the Result_vector . [3]
  14. 14
    Select something from the drop-down list. The info should automatically change.
    15. 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. For example, we can use Book3.xls to do a comparison and create one column containing the products, and one with the difference of these products between both years. [4]
    • C:\Compare\Book3.xls (containing a sheet named “Comparison”)
  3. With only “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. For example, type the following 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.
  7. This will get all the differences.
    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

      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
      Play Minesweeper
      How to
      Add the Google Play Store to an Amazon Fire Tablet
      How to
      Open EPUB Files
      Using Rufus to Create Bootable USB Drives: A Step-By-Step Guide
      How to
      Make an Exe File
      How to
      Save an Animation in Blender
      2 Simple Ways to Create an RSS Feed
      How to
      Turn on Microsoft Defender in Windows 10 and 11
      83 Fun Things to Do Alone at Home or Elsewhere
      How to
      Add a Password to a RAR File
      How to
      Edit DLL Files
      2 Easy Ways Connect the Kindle Fire to a Computer
      How to
      Crack Software by Modifying DLL Files
      How to
      Use Clonezilla Software
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/basic-tasks-in-spreadsheet-compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8
      2. ↑ https://edu.gcfglobal.org/en/excel2016/creating-and-opening-workbooks/1/
      3. ↑ https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb
      4. ↑ 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 362,930 times.

      Is this article up to date?

      Advertisement