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.
There is so much advertizing garbage on this page that is it impossible to find the actual product.
ReplyDeletethanks very much
ReplyDeleteThanks for the the link to "Spreadsheet Compare". But what about this "Download Manager" ad on the top of this page? When (accidently) clicking on it, nasty stuff gets downloaded and will spread all over the harddrive. Horrible! Kinda worrying that GoogleAdservices will link to such crap.
ReplyDeleteWhen I try to run the add in I get a VB error that says "Run time error 459, Can't move the focus....."
ReplyDeleteI doesn't work on MS Excel 2007. It returns
ReplyDelete"
Compile error:
Can't find project or library
"
It stops right here
Private Sub LoadSettings()
It worked really well for me. Thanks for this macro
ReplyDeleteIt stops here in excel 2007 for me
ReplyDeletePrivate Sub LoadSettings()
Why does this try to connect to the Internet? Do you know antecedents of the author?
ReplyDeleteOh this is just awesome! In my office I daily need to compare some spreadsheets. Previously I did this by using excel formulas like "=A1=B1", but this method was too tedious.
ReplyDeleteYour add-in will definitely help me. Thanks for sharing such useful information.