Sep
15

How to compare and find differences in Excel worksheets?

In our today’s review we will have a close look at the problem of comparing Excel sheets and will try to find the best plug-ins to compare MS Excel workbooks and find differences between them.

If you are working with product catalogues, price-lists, database reports and other stuff like that, such add-ins may become your worthy assistants and will free you from monotonous and tiresome manual processing of piles of Excel files and documents. They will help you keep track of changes, make amendments and synchronize your Excel files with a couple of clicks.

Today we are going to review three plug-ins purposed for finding differences in Excel sheets that are distinguished from others of this kind by their rich functionality and user-friendly interfaces. It is these criteria that were put on the top of the list in this review.

Add-in Version Vendor Price Excel supported
Compare Spreadsheets for Excel 1.1.1 MAPIlab $59 Microsoft Excel 2000 / XP / 2003 / 2007, .NET Framework version 2.0 required.
Excel compare 2.3b Formulasoft $34.95 Microsoft Excel 97 / 2000 / XP / 2003 / 2007
Synkronizer 9.1 XL Consulting GmbH &eur;69 Microsoft Excel 97 / 2000 / XP / 2003

Compare Spreadsheets for Excel from MAPILab

On the Compare Spreadsheets for Excel web-site you will find an excellent description of the main function of the plug-in: automatic comparison of Excel worksheets and visual presentation of their differences. The usage of the add-in is illustrated by the practical example of comparing two price-lists for different months. The comparison process is shown step-by-step with lots of screenshots. I think after you look through that page you will hardly need to refer to Help. All functions and operations are presented very intelligibly. Well then, let’s check if the vendor’s statements are true.

At the first step of the installation wizard the user can choose the language (in their current version English and Russian are available).

Compare Spreadsheets for Excel installation wizard: choosing the language.

Then, like in all other MAPILab add-ins, you select whether to install the add-in for you only or for all users of the PC.
Compare Spreadsheets for Excel: Customer information.

In case you have quite a lot of Excel add-ins installed, the next screen will help you not get lost among plenty of icons and buttons by showing where to look for the Compare Spreadsheets toolbar in your Excel (bordered in red in the screenshot):
Compare Spreadsheets for Excel: First run window

As shown in the screenshot, the Compare Spreadsheets for Excel toolbar has two buttons. A click on the Help button will open a drop-down menu:
Compare Spreadsheets for Excel: Help menu.

A list is quite standard for any MAPILab add-in but for one new item. The Language option allows you to change the interface language on the fly. As for the rest, all is done on a traditionally high level: built-in Help with screenshots and information about your license type and the number of your current version.

When you click on the Find Differences button (which is the left one on the Compare Spreadsheets for Excel toolbar), the main window of the add-in will show up:
Compare Spreadsheets for Excel: Main window.

Here, as they say, all speaks for itself. Left range – you select your first workbook, sheet and the region for comparison, Right Range – select a second workbook, sheet and the region for comparison. In case both needed workbooks are opened, all their sheets are available by Browse and Sheet drop-down lists. So, you can easily select required files from the lists without wasting your time on specifying a full path to the sheet.

If the information you entered is not complete, you will see the following warning icons:
Compare Spreadsheets for Excel: Sheet alert.

A region for search is selected with the mouse:

Compare Spreadsheets for Excel: Range selection.

Also, during the comparison process the add-in automatically crops empty cells:
Compare Spreadsheets for Excel: cropping empty cells.

At the next step you specify Aligning options. Each of the available options is supplied with detailed explanations, so you will barely have any difficulties.
Compare Spreadsheets for Excel: Aligning options.

Match options come next:
Compare Spreadsheets for Excel: Match options.

At the next step you choose the look of the final report. And here the Compare Spreadsheets for Excel add-in gives us a really nice solution – colored cells:
Compare Spreadsheets for Excel: Report options.

The final Difference report has the following look:
Compare Spreadsheets for Excel: Difference report.

As you see in the screenshot, all the cells that differ in some way are colored or highlighted. The following options are available: synchronize views, synchronize selections, as well as toogle vertical and horizontal orientations.

Search in a trial version is limited up to 30 rows or 10 columns.

Well, what do we have? First-rate, easy-to-use add-in for comparing Excel sheets. To tell you the truth, I didn’t notice any drawbacks or flaws in the work of the add-in. The wizard based principle of work is totally justified, the result presentation is excellent, difference reports are clear, lucid and easily customizable.

Excel Compare from Formulasoft

The Excel Compare web-site brings to your attention a list of main functions of the Excel Compare plug-in:

  1. Support of all Microsoft Excel versions (from Excel 97 to Excel 2007).
  2. Comparing sheets that have a database structure.
  3. Special comparison algorithm for the data that do not contain unique identifiers.
  4. Comparing any selected ranges of an Excel worksheet.
  5. Comparison of all files located in two different directories.
  6. Comparison of all sheets with the same names in two workbooks.
  7. Creating a Difference Report as a new Excel sheet.
  8. Difference Report keeps the format of the input files.
  9. Highlighting the changed cells.
  10. Dividing the report into three parts: deleted, added and changed data.
  11. Automatic addition of user-defined macros to the report workbook.
  12. Support of Project settings.
  13. Supports of Command Line options.

So, if to cut off from this list functions available in all other comparison add-ins, we’ll have a number of unique and quite interesting features: automatic addition of user-defined macros, ability to carry out several comparison operations at a time (using tabs), the idea of the project (in the framework of which you compare your Excel files).

How does the idea of creating a new project every time you need to compare some data sound to you? Are you going to save the project for future use (even so you will have the comparison results in a Difference report anyway)? For me all this seems rather doubtful and too complicated if we deal with daily office tasks. It’s quite another matter if the format of your Excel file remains the same and only data is subject to change. As a rule this is typical for some technical reports.

As for the ability to create several tabs with comparison results, to my mind, it is the comprehensibility of results that matters. Let’s see how all this stuff gets along together in one add-in.

Well, Excel Compare creates its own group in the Start menu and places its icon on the Quick Launch toolbar, which suggests that it’s not an add-in but rather a stand along application. If you try to run the Excel Compare add-in with your Excel closed you will see the following message:

Excel Compare: loading error.

However the add-in’s main window will show up:
Excel compare: Main window.

The Sheet Name and Range fields make you think you’d better have your Excel opened.

When I tried the tool with an opened Excel workbook, no error message appeared, but I was a bit dissatisfied with not having seen the opened workbook in the drop-down lists of First file and Second File. You will have to enter a path to the book manually.

As for comparison options, there are no comments or explanations.
Excel Compare: Project options.

This is the case when you will need to refer to Help. The add-in has a built-in Help system supplied with screenshots and divided into sections. There is a step-by-step instruction for each comparison operation: comparing databases, versions and all workbook sheets.

Report options are available on the Report tab:
Excel Compare: Report project options.

In the Difference report you can use different colors only for data coming from different input files.

The Macros tab has the following look:
Excel Compare: Macros project options.

Now it’s time to turn our attention to Difference reports. If you open the Excel file for the first time, you will have to enter all paths manually. As to the region, you can select it with a mouse or manually:
Excel Compare: Selecting range for comparison.

In case you use only one tab, the Difference Report looks like this:
Excel compare: Difference report.

I must admit that when I did the same work with the Compare Spreadsheets for Excel add-in from MAPILab, I didn’t have any problems with understanding the Difference report. Well, may be such report layout is more preferable in case of databases comparison, but for my routine office tasks I prefer something more straightforward and visual. Now let’s try to compare two column pairs in different tabs.

Although the information about the first pair of columns has already been entered, you have to fill all fields of the second tab manually.
Excel compare: Difference report with 2 tabs.

As far as I understand, the difference report from the first tab just disappeared. When I clicked on the Compare button I was on the second tab. And this is a difference report for the second pair of columns.

Thus, if you work with office documents, this Excel Compare add-in doesn’t look quite fitting. If you are a database administrator or such, this plug-in will free you for a while from the necessity of learning to write macro and scripts yourself.

Synkronizer from XL Consulting GmbH

The Synkronizer web-site will tell and show you the main features of the add-in with the help of screenshots complemented by detailed comments. You will also find some practical examples of usage of this Excel comparison and update add-in. Their practical examples are given for people of different professions and probably you will find a couple of other applications for this plug-in.

At the installation start, you will be asked to choose the language. In the current version, English and Spanish are available:
Synkronizer: Language selection.

After running Excel, I was able to see the add-in only in the Tools menu, but not on the Excel toolbar:
Synkronizer: Add-in menu.

The first window you see after starting Synkronizer gives you details about available versions, their differences and prices:
Synkronizer: Evaluation screen.

The main window of the Synkronizer add-in has the following look:
Synkronizer: main window.

When the main window of the add-in shows up, all opened Excel files get minimized to tray (later on it doesn’t happen any more, even at the add-in start up). Luckily, there are dropdown lists containing currently or previously opened Excel files from which you can select the sheets needed and they will be immediately opened. Surprisingly, the add-in doesn’t allow selecting regions for comparison.

The final report looks like this:
Synkronizer: found differences.

Synkronizer: test sheets after comparison.

Such look of the final report suggested the idea that the data layouts in both workbooks should be identical. Let’s check this by interchanging columns. To its credit the add-in stood the test. The result was the same and all data were matched correctly.

And here how the Setting window looks like:

Synkronizer: Settings window.

The add-in can compare the following data types: values, formulas, comments as well as formats. Also, you can have the comparison results highlighted. The ability to have a detailed comparison report is worth your close attention as well:

Synkronizer: detailed comparison report.

You can manage the comparison results using a special navigator:

Synkronizer: Navigator for managing comparison results.

With this Navigator you can make changes in your Excel workbooks and sheets, switch between windows, count and display changes and much more.

In the Settings tab you can see lots of pleasant and interesting things: filters to trim spaces, to ignore data types, to make comparison case-sensitive or insensitive, to ignore invisible rows and columns and the text filter:

Synkronizer: filters.

You can also make use of numerous options for customizing comparison reports:
Synkronizer: report options.

You can get complete information about all options and feature of this plug-in in the built-in Help which is available from any tab by clicking on its icon.

Thus, Synkronizer from Excel Compare simply doesn’t have equal competitors in our today’s review. All said above about the Compare Spreadsheets for Excel add-in from MAPILab in the same degree is true for Synkronizer 9.1. Moreover, Excel Compare managed to make their add-in even more flexible and fast. I think this will be s the right choice.

Compare Spreadsheets for Excel Excel compare Synkronizer
Installation 5 of 5 4 of 5 5 of 5
Usability 5 of 5 4 of 5 5 of 5
Functionality 5 of 5 4 of 5 5 of 5
Help 5 of 5 4 of 5 5 of 5
Total 5 4 5

Comments are closed.