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:
The tool also allows you to automatically generate a very handy track-changes report:
After installing the plug-in from the link above, proceed as follows:
- Open the Workbook or Workbooks to be compared.
- Start Spreadsheet Compare.
- 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.
- On the ‘Processing Options’ form, select the processing options that you want:
- Start Row – Starts the compare from a particular row (useful when running long compares that have failed because of a mis-match).
- Delete Change Column – Self explanatory.
- Clear Existing Sheet Colours – Removes any cell colouring from the worksheet.
- 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.
- Add Change Column to show – Adds a column to the spreadsheet to indicate changes.
- Mismatched Column Name – Useful if the first row is a column name (for DB comparisons).
- Count of Mismatched Cells – Self explanatory.
- Eye-catchers – Self explanatory.
- Highlight changes with: – Set a colour to highlight the changes.
- 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.
- 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.
- On the ‘Final Options’ form, check any final options:
- Stop on miscompare – stops on any miscompare.
- Stop on miscompare in the first column only – self explanatory.
- Generate Report – self explanatory.
- 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.