Sometimes you may need to compare two Excel sheets for difference in data. Manually doing this would be extremely tiring particularly if the tables are large. The easiest way to do this is to use the free Spreadsheet Compare (Excel compare) add-in for Microsoft Excel.
Spreadsheet Compare performs a cell-by-cell comparison of Worksheets within the same or different Workbooks and then highlights the changes on the sheet, or prepares a new sheet report showing all the changes the program has found. The add-in is written in VBA and is compatible with Excel 2000 and through every version till Excel 2010.
After installation, the Spreadsheet Compare function becomes available under the Add-in tab in MS Excel’s ribbon. You will notice two buttons here – Compare and Sync Scroll. Click the Compare button.
A step-by-step wizard will guide you through the process which is, briefly, as follows: you select the two spreadsheets to be compared, select the start and end row, set sheets to ignore (if any), specify data ranges to ignore (if any) select report type, choose highlight colors and if necessary, stop the program once the first mismatch is detected. If you don’t have any changes to make in the configuration, you can skip all the steps and jump directly to ‘compare’.
After the comparison is done, Spreadsheet Compare highlights the differences found on both sheets and adds a column to show you which columns have been changed.
It also generates a detailed comparison report.
And a Merged Difference report.
The other function of Spreadsheet Compare is to enhances the Synchronous Scrolling feature which was added in Excel 2003, and is incorporated via the ‘Sync Scroll’ feature. Say the spreadsheets you wish to compare are not properly aligned, i.e. one of the sheet has additional rows or missing rows. The Sync Scroll feature allows you to synchronously scroll the two spreadsheets and checked for added or deleted rows. Rows can then be added so that all the matching rows are correctly aligned before performing a comparison.
Spreadsheet Compare should be very useful when working with large Excel sheets, and can potentially save you enormous amount of time.