I downloaded and trialed 5 excel comparison tools this week and thought I’d share my thoughts on them – they seem to fit into 3 camps, text based, link based and excel based. My review is going to be skewed toward my criteria as it was very specific. Here’s my criteria
- As my spreadsheets are HUGE (this one has over 30 sheets, but one I have has over 200 sheets in it), I needed one that would cope with big and cumbersome spreadsheets (don’t worry they will be FIXED on the next project).
- I just needed to be able to compare values, not formula as i had checked most of the formula’s previously and there seemed to be no major formula consistency issues.
- I needed to have the results either appear in the spreadsheet OR link to the spreadsheet as I needed to see the differences in context of the surrounding data.
Here are the products I tried:
- Excel Compare by Formulasoft
- Synkronizer – looks like it’s by a Swiss company
- Diff Doc – It’s more of a text based comparison but it did say it compared Excel
- 4 Tops Excel Comparison
- Component Software ExcelDiff
- Florencesoft DiffEngineX
I quickly discounted Diff Doc – it had a great slick website but it was a text based comparison tool that meant it converted the spreadsheet to text first before comparing. I just tried a simple summary spreadsheet and it showed all the columns in the results window not lined up, so it was basically impossible to use.
ExcelDiff and 4 Tops re-create the spreadsheet into a HTML report with hyperlinks back to the spreadsheet – useful but it doesn’t help to show the context of where the differences are and not having the underlying spreadsheet’s formatting is not helpful.
Excel compare is similar to above but presents the differences in Excel, but still lacks the ability to see your whole spreadsheet in i’s original context with the differences shown.
DiffEngineX has a great feature that allows you to insert rows and / or columns to make the 2 spreadsheets layouts the same. It’s almost worth buying it for that. It also shows the differences directly in the spreadsheet. I’m not sure why I discounted this one, it may be just that Synkronizer did 95% of what I wanted.
So, Synkronizer was my winner. On comparing one sheet it took just a few seconds then displays the differences hilighted in the colours you choose – different colours for inserted and deleted rows and columns, different values and even different formula’s (this could come in handy). The program removes all your cell hilights first but keeps all other formatting in place. When finished it shows a navigator bar to navigate through each change (handy for a small number of changes but no so handy for my 3000+ changes that it eventually found).
I’m not yet 100% convinced that it finds EVERY change, and if you’ve inserted a row and changed something in the same region it doesn’t pick it up, but those are small issues that are outweighed by the excellent results it produces.
In my 30+ sheet workbook it only took under 5 minutes to run and then I had two spreadsheets fully highlighted with all the differences, AND a separate map with all the changes hyperlinked back to the source spreadsheed.
I would suggest when using this, to save your two workbooks as different names before using it. You can also merge the values to the two spreadsheet but I wouldn’t recommend it as it only does values, not formula’s so I would be very careful with this feature.
So for 69 Euro I think it’s a bargain and I’m going to buy it to run it on my spreadsheets at the end of inputting data each month as a double check that everything I have changed is what I was expecting to change.