Comparing Excel files

Even though Excel boasts a decent track changes feature (in Excel 2007-2010, it sits under the Review tab, and then Track Changes > Highlight Changes), we are still missing a Compare Document tool as in Word.

Spreadsheet Compare is a free Excel plug-in that allows you to perform cell based comparisons between two workbooks. The differences will be highlighted in yellow, like so:

 highlight

The tool also allows you to automatically generate a very handy track-changes report:

 report

After installing the plug-in from the link above, proceed as follows:

  1. Open the Workbook or Workbooks to be compared.
  2. Start Spreadsheet Compare.
  3. On the ‘Select two spreadsheets to be compared’ form, select the Workbook(s) to be compared. To compare two worksheets within the same workbook, select that workbook in both drop down list. Click next.
  4. On the ‘Processing Options’ form, select the processing options that you want:
    1. Start Row – Starts the compare from a particular row (useful when running long compares that have failed because of a mis-match).
    2. Delete Change Column – Self explanatory.
    3. Clear Existing Sheet Colours – Removes any cell colouring from the worksheet.
    4. Case Sensitive Comparison – self explanatory. If this is unchecked, the cell values read are converted to strings and the changed to upper case and compared. Should be checked by default.
    5. Add Change Column to show – Adds a column to the spreadsheet to indicate changes.
      1. Mismatched Column Name – Useful if the first row is a column name (for DB comparisons).
      2. Count of Mismatched Cells – Self explanatory.
      3. Eye-catchers – Self explanatory.
    6. Highlight changes with: – Set a colour to highlight the changes.
  5. On the ‘Select Worksheets from First Workbook’ form, if the workbook contains worksheets that you do not want to compare, select them and click the remove button. If you are comparing worksheets within the same workbook, remove one of the two worksheets that you want to compare. Click next.
  6. On the ‘Select Worksheets from Second Workbook’ form, if the workbook contains worksheets that you do not want to compare, select them and click the remove button. If you are comparing worksheets within the same workbook, remove the worksheet kept in the previous form. Click next.
  7. On the ‘Final Options’ form, check any final options:
    1. Stop on miscompare – stops on any miscompare.
    2. Stop on miscompare in the first column only – self explanatory.
    3. Generate Report – self explanatory.
  8.  Click ‘Start Compare’

The only downside of this tool is that the comparison is made at the cell level, not at word or character level, and as linguists we all know too well that Excel tends to double as a word processor these days.

I have tried a couple of different tools which claimed to be able to compare Excel files at a more granular level, namely Beyond Compare and Araxis Merge, but the results were not satisfactory at all with my sample files (the results were either inaccurate or complete gibberish, even with nearly identical files). Mind you, these tools have other very useful applications (e.g. comparing folder structures or tagged file formats) but I can’t recommend them for this purpose.

If a cell-based comparison won’t do for you, you may try to copy the contents of the two worksheets to Notepad, then to MS Word, save them as two separate files and run the Review>Compare function in Word.

Advertisements

3 thoughts on “Comparing Excel files

  1. Sheila says:

    How do I actually download the file, can’t figure it out?

  2. Kossem says:

    To Sheila:
    Click on the link at the beginning of the second paragraph ‘Spreadsheet Compare’, which is:
    http://sourceforge.net/projects/spreadshcompare/
    In that page, simply click on ‘Donwload’, then run the exe file.
    After (re)starting Excel, you should see the installed Compare Add In appear under ‘Add-Ins’ in the Ribbon.

    Btw, I have just installed this program and I gladly report that it works just wonderfully.
    My thanks to the developer.

  3. Jesús Huerta says:

    If you have Excel 2013 or newer, you can use “Spreadsheet Compare” to compare two workbooks:

    http://www.sysadmit.com/2015/10/excel-comparar-ficheros.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.