How to Compare Two Excel Files Without Writing a Script

How to Compare Two Excel Files Without Writing a Script.

At a certain point, most excel users will come across the challenge to compare two excel files for differences, and to merge or update these differences. You can either sort and compare two excel spreadsheets for differences manually or you can write a script that shows the differences. Both ways will be time-consuming and, most likely, will be difficult to ensure, that all differences have been found. Fortunately, there are several excel compare tools which, which compare two excel sheets and highlight differences and support you to update a file or to merge two excel spreadsheets.

How to Compare Two Excel Files Without Writing a Script

In this post, I will show you, how easy and fast it can be to work with an excel comparison tool without the need to compare files manually and without writing a script. But first things first.

Here are a couple of classic examples of an infinite list, why to consider buying a tool rather than writing your own script manually:-

1. Update the price-list on the website.
2. Merge multiple customer lists into one main excel file.
3. Update the product inventory.
4. Merge multiple time entries into one time-sheet.

To merge excel files is usually done in two steps:-

1. The first part is to compare two excel files for differences and to highlight the identified differences.
2. The second part is the actual updating and merging process where single differences or entire difference groups are transferred from one file to the other file.

Each of the two steps has its own challenges. As for the first step, identifying differences, the main difficulty is to find really all differences. This seems to be relatively easy as long, as both files are pretty much the same. But as soon as columns or rows are inserted, it is getting very tricky. In both cases, you would need to spend time to make both files the same before you even can start the comparison script.

For me, it is really the question, why to spend so much time to write a script if you can get it all done pretty much automatically. A good excel compare solution will cost around 100 USD and will support you in identifying differences as well as in updating and merging files.

To demonstrate, how easy and fast it is to work with an excel compare tool rather than writing a script I use the example of updating the product inventory including updating prices and the tax codes, which is a very common task. In my example, I use Synkronizer 11 as the excel comparison tool.

As the first step, you will need to select your files to be compared and, depending on the software you are using, select the basic settings and filter options which will make the comparison more accurate and easy to work with.

Select the files you want to compare.

Image – 01

In our example, I have a file called “Inventory_January” which is the status by the beginning of the year and a file “Inventory_December” which is the manual count of the inventory by the end of the year and the manually updated prices. How to Compare Two Excel Files Without Writing a Script.

Image – 02

A feature that is quite useful is the option to choose the comparison mode as either worksheet or as a database. Depending on the mode, the specific settings will make the results more accurate and easier to read. However, in my example, I choose the worksheet model and to compare the entire worksheet rather than just a selected range. How to Compare Two Excel Files Without Writing a Script.

Image – 03

Now before you hit the compare button, let me explain briefly some additional setting and filter options.

First the option to include Excel names and comments.

Image – 04

This setting allows us to include format differences that are disabled by default since in many cases, highlighted format differences will be distracting.

Image – 05

One of the most important features is the filter setting. By filtering out certain types of differences it will make results much easier to read and enables you to focus on the differences which are essential to your task. Since various tasks require a different set of filters, a custom script will either not allow filtering, only use one set of filters, or will require a modification of the script for each task.

Image – 06

And finally, the option to select the outlining options.

Image – 07

Even there are many setting and filter options, in many cases you will not need to set them and can right away jump into comparing the two excel files.

And here are our results, which took less than a second to be produced. On the left side, you see the main navigation and on the right side, the two worksheets with the differences highlighted.

Image – 08

To get an easy start, this excel compare tool shows the number of differences for each type of difference, for example, inserted columns, inserted rows, value differences or differences in calculated values. Of course, there are many more different types, but since there are no differences, it is suppressed and not shown.

The summary in my example, tells, that 50 differences have been found and all differences are entered values. How to Compare Two Excel Files Without Writing a Script.

Image – 09

At the lower part of the navigation, you get a list of all the differences which show you the cells where a difference occurs and the actual difference. Parallel scrolling and linked differences make it very easy to navigate and to sift through the differences.

Image – 10

With the identification of the differences, the first step of the task to compare two excel files for differences is completed. Unfortunately, most excel comparison tools end at this step and only very few tools are capable to automatically merge or update files. It is nice to find differences instantly without writing a script, but in my opinion, it is only just halfway there.

However, the tool I am using in this example fully supports the updating and merging of excel files and it is the only tool available, that is capable of correctly identify inserted columns and inserted rows, which of course can be also updated automatically. In this example, it shows you the options on how to update the differences. You can do that all at once, or on a case by case basis. This makes sure that the merger of the two files is 100% accurate or in other words, that it is exactly the way you need it.

Image – 11

Since we need to update the prices and the inventory from the right worksheet to the left one, we basically just can click “transfer all differences from the source worksheet to the target worksheet” and the merging of the two files is done. One single click – It does not get better than that! With this click the file “Inventory_January” includes all correct number of items and all prices are updated.

Image – 12

All that is left now is to save the updated worksheet.

The whole process from beginning to end took less than five minutes. With this fantastic time saving it is obvious, that the comparison of two excel sheets does not require a custom script. All it takes is to select an excel compare tool that fits your needs.

How to Compare Two Excel Files Without Writing a Script

See Also. 

Here, You may wish to read more articles-

• Microsoft Excel Archives @ BBALectures
• Mail Merging with Microsoft Excel and Word
• How to create Data Entry Form in Microsoft Excel 2010