The Detail Department

You are here: Home / Excel / Excel Comparison Tools Reviewed

Excel Comparison Tools Reviewed

by

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:

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.

Comments

  1. Marisa says

    In reply to “Babi Seal” – you should try out DiffEngineX. It is free to use for the first 30 days. The download URL is

    http://www.florencesoft.com/excel-differences-download.html

    I’ve tested DiffEngineX myself with half a million rows in an Excel workbook and it works fine.

    If you want this claim verified, contact the developers via their website and I’m sure they will happily provide a test workbook with 500, 000 rows filled up.

  2. DoG says

    As you see I’m not a spam and I do a LOT of diffing. I do require a strong diffing tool.

    I did (IMO a better) investigation on diff tools, together with my colleagues, and in different companies, for several years. I still claim BC is easily the best tool for comparing Excel tools (and also files in general). If you compare massive sheets and want realtime setting changes ASAP (I mentioned such requirements in my previous post), there’s no better tool to do it. I’d be happy to inform you about any better alternative but I had not such luck, so I will try to explain why BC.

    If you’re diffing from time to time and don’t have big ambitions, pick any of the tools suggested. If you do a lot of diff analysis, read more.

    To be specific, what diffing capabilities you may require sooner or later;
    – people who truly perform diffing, need a tool which is able to handle huge files (BC performs best here, other tools collapse – see also Babi’s post to get an idea)
    – such people also need batch diffing (BC will compare whole folders for you)
    – sometimes you want to make some columns unimportant because they contain remarks, text labels that change frequently etc – and want to rediff and see the impact immediatelly (we’re not working for social institution and don’t have days to do this right…) – BC performs best here
    – sometimes you want to change diffing accuracy on numbers (tolerate small number differences), and again, see the impact immediatelly – BC performs best here
    – you want to set priorities on what columns should be compares first (something like name, surname, phone number..) – BC handles this
    – you need speed, a standalone tool, not an Excel application plugin that reuses Microsoft code – BC is ways faster than all those plugins
    – you want to create reports, show only the differences, paste from clipboard etc etc – BC makes it possible.

    BC does a lot of other ways of diffing, which makes it one of most useful tools for professionals. Don’t worry I’m not loving it endlessly. For example, for XML diffing and real time editing, I prefer Araxis 3way compare. I hope this helps you to find your tool for your job. Cheers.

  3. Babi Seal says

    I did buy Synkrocity but it ran out of memory on a pretty high-end Intel box with lots of memory.

  4. Marisa says

    DoG’s comment isn’t entirely correct. I have some familiarity with DiffEngineX. DiffEngineX isn’t a plug-in – it runs outside Excel. It does not pre-sort, but you can set key columns (when Align Rows is checked). It doesn’t ignore columns though, but it isn’t hard to either delete columns in a temporary copy or just copy out the important parts of your worksheet into another worksheet.

    DiffEngineX actually does a lot. It compares the Visual Basic for Application macros, Excel defined names and comments and does have other features such as the ability to ignore small numeric changes. It actually does quite a lot and DoG is unfair to discount it.

    I haven’t looked at Synkronizer for some years, but I’m sure, to some extent, it should not be discounted either.

  5. Jodie Miners says

    I think this is a spam link because I can’t verify if the commenter is a real person or not. But I’ve had a quick look at the website and I will let you, my readers determine if you think Beyond Compare is a valid Excel comparison tool. It is a file diff tool – there is a plugin (or Rule) to compare Excel files see http://www.scootersoftware.com/download.php?c=kb_morerules – it looks like it could be handy but at $50 per licence it would need to be good.

    For general file Comparison I use the Open Source free product Winmerge – which also has an Excel plugin – see here http://manual.winmerge.org/Plugins.html#Plugins_available.

  6. DoG says

    These tools are just useless. It’s all just little plugins, unfortunately driven by Excel, without any powerful functions like pre-sorting, tolerating little differences, realtime editing and recompare, key column setting, ignore columns… I do real diffing of XLS files and nothing outperforms Beyond Compare. I’m happy to hear about a better tool, but the ones mentioned here are just piece of crap.

  8. Don Ramler says

    Found your comments very useful. I am looking at Excel Compare and Synkronizer and found Synkronizer to be very good. Since your blog was posted in 2006, I would like to know what you have found since that blog.