Combine/synchronize Excel sheets

When several people work simultaneously on one single Excel document, things may become a little difficult if everybody is doing modifications (or trying to do them) on their own. It is usually necessary to bring some tool in, if you want to handle correctly the comparison between all the modified versions and to lead/guide you in the process of re-synchronizing all changes in one common file (obviously some of the modifications may be in conflict with each other).

For the simplest files (text files) there are a few famous tools (on Windows PC, there is the excellent WinMerge freeware that presents -side by side- both files and allows to quickly compare and choose which changes to keep or not).

WinMerge offers a plugin to check the differences between Excel files but it does not allow to combine changes back into the original.

But when reaching more complex documents like Excel files, WinMerge is no longer enough and you need a specialized tool. I believe that there is no free software tool for the task. However, there a few shareware tools (here you should understand “cheap, but not free” or “paying, but not expensive”).

  • Monkey Merge 1.29 ($16, free to try). Merge Word, Excel and PDF docs
  • SobolSoft Excel Merge (Combine) Cells, Columns & Data Software 1.1 ($19.82, free to try). Join, consolidate selected cells by row or column. Cell merging allows content of many cells to be put into one. Cells content is seperated by a space, comma or user-defined character(s).
  • Diff Doc 2.45 ($24, free to try)
  • Compare suite 3.0 ($60, free to try)
  • Synkronizer 9.1 ($149, free to try), only the Developer version provides for combining comparison results
  • Excel Compare ($30), only produces a comparison report. Not evaluated.

So, I tested several of these tools. Here are the ideas I extracted from this work.

Monkey Merge did not recognize my Excel 2003 files. I uninstalled it in a matter of minutes.

SobolSoft software is a nightmare of a nagware (it is often difficult to find the application behind the veil of reminders and messages). The evaluation was very short too: A cryptic error message any time I tried to run it.

DiffDoc is much more usable in real-life. It accepts about ten different languages for its interface (pleasant/useful/necessary for an application to distribute in Europe). If there are many options and this leads to a little apparent complexity on first contact, it is re-assuring to see that the tool can limit itself to only some Excel folios or some cell zones.

DiffDoc behaviour seems quite sane for the comparison task (it is quite fast, clearly points at the additions and removals, the markings are clear (colors) even if the format is crude (far from a full Excel format) but somewhat a little difficult to read if the file contains a lot of formula-based cells like my test files). But only providing file comparison is a strict limitation (you would like to know how to produce a direct Excel output from combining observed/chosen modifications).

In Compare suite, comparison is fast and immediate. you open the two files and it handles their contents in a very readable form reminding me a lot of WinMerge. The formating is no par to Excel, of course. But it is close enough to easily recognize your original files while you work on them.

You can apply the changes to one file or the other and save them (even in two independent files). Very interesting and fully natural, but Compare Suite never accepted to save anything in Excel format despite my first impression and always proposed me with text-only options. A very good first impression, followed by a disappointed feeling.

Finally, with Synchronizer 9.1, the approach is different since the software installs itself inside Excel (there is no easy way to start it from the Start menu of Windows). When you run Excel for the first time after the installation of Synchronizer, you will find it at the bottom of the Tools menu. First very obvious advantage: Files to be compared are open inside Excel and their formatting is visually perfect. Additions, removals and modifications are crystal clear (even better than in Compare Suite).

Again, the software does not provide the synthetic merge and you need to manually copy from one file to the next, but the operation is definitely easy since you are already inside Excel (no need to switch from one tool to the next). It is a little disappointing but Synkronizer 9.1 obviously does a lot more than most others and in such a convenient way that the others pale in comparison (pun intended).

The question left open is whether or not the price of the Synchronizer Developer version ($149, mind you) really can be justified by the added confort; It cannot even be tested in the trial version (that is a trial for the $29 Light version of the software).

However, here, I decided to select Synkronizer 9.1 Light version for the task at hand: Small modifications for a long table shared between 2-4 users.