To compare two excel files for differences you can either do it manually by sorting and by applying certain formulas, you can write a script, or you can choose an excel compare tool which will make this task quite easy and fast.
At a certain point, most excel users will come across the challenge to compare two excel files for differences and, in most cases, to merge or update these files. In this tutorial, I lead you to step by step through the process to compare two excel files for differences and will cover the updating and merging process in one of my next articles. For this post, I am using Synkronizer 11 as an excel comparison tool, but there are several other solutions to choose from.
As the first step, you will need to select your two excel files to be compared. In most solutions, you can either choose a file from past projects or you can browse for your files.
Even though in Synkronizer the first worksheet is labeled as “source worksheet” and the second worksheet as “target worksheet” these labels are only for orientation and it does not matter which file you select as the target and which one as the source.
Before you can start to compare two excel spreadsheets, a good solution will validate if the names of the worksheets match. This is probably less important when having only one worksheet to compare, but as soon as there are multiple worksheets in both files or if one file has more worksheets than the other, this step becomes crucial.
In my example, the first workbook has one worksheet and the second workbook has two worksheets.
In my example, the names of the two worksheets do not match and will, therefore, need to be matched manually. Simply select the matching worksheet and the arrow beside the selected worksheet will turn red. Click the red arrow to apply the manual matching of the two worksheets. If all worksheet names match, this step will be done automatically.
Once the matching is applied the two worksheets are shown as a pair and a window with additional comparison options at the bottom of the main navigation is displayed.
Of course, multiple worksheets can be compared at the same time. However, once the worksheets are matched we are ready to start the comparison process. But before we hit the start button, a couple words about additional options and settings, that can make it quite easy to compare two excel spreadsheets and that will improve the accuracy and the readability of the comparison results.
Select and compare ranges
This option allows, to only compare a selected range within a worksheet. This function can be used for either very large worksheets in which only a small part or only a few columns need to be compared or for worksheets with a slightly different layout but the same data and structure. For example, a table starts at A1 in the first worksheet and the same table starts at B5 in the second worksheet. In such a case it is recommended, to select the range in both worksheets individually to avoid the need of formatting and aligning the content of the two worksheets.
Comparison as a worksheet or as databases
In this tool, you have the option to either compare a file as a worksheet or as a database. Especially when comparing databases, this option becomes a great asset. However, since the comparison of databases will be covered in a separate article, the current example files will be compared as standard worksheets.
Include contents in the comparison
You can select if you want to include cell comments or/and excel names. By default, comments and names are not compared. Basically, Synkronizer excludes everything, that could distract from focusing on the relevant differences. The more different types are highlighted, the more difficult it can be to read and understand the results.
Enable format comparison
Here you can choose what format differences should be included in the comparison. For example, different fonts or font colors, etc. By default, the format differences are excluded from the comparison since it might distract from focusing on the relevant differences. The option that each format type can be selected individually lets the user really fine-tune the comparison task and its results.
Set filters to focus on relevant results
The filter setting option in Synkronizer is quite comprehensive. Setting filters will reduce the number of differences and enables to really focus on what differences you want to see. Simply click “enable filters” and choose, which type of difference should not be shown in the comparison.
Choose the report format
Sometimes it is quite important to create a report to document the differences. This documentation can be done as a standard excel report or as a hyperlinked report. Of course, all reports can be further customized depending on your needs.
Choose and customize how differences should be highlighted
Here you can set, whether you want to highlight differences, which of course is recommended or if you don’t want to see the differences with a highlighting color. The option “Clear & highlight differences” means, that the background of an excel file is deleted and only the highlighting color is shown. The downside of using “Clear & highlight” is, that if you save the compared document, the background is erased and cannot be brought back.
To make the readability of results even easier or more user-friendly, the color coding can be chosen individually for each different type or as a pre-set color scheme, depending on your preferences.
All these settings are of course optional. But using these filters and setting options will make it way easier to read, understand and use the results of a comparison task. All that is left now is to hit the start button which will compare the two excel files instantly.
In the case that you need to change or adjust filter settings, reports, highlighting or outlining, simple change the setting options and click on the refresh button. If you don’t need to refresh all the worksheets, then you can select a specific worksheet and only refresh the selected one.
The results of a comparison task are produced, depending on the file size, within seconds and displayed in a single interface. The good thing of having all in one interface is, that you can easily navigate from a different type to an individual difference and updated or merge them step by step while always seeing the result of the change on the right side in the worksheets. How to update and merge two excel files I will show you in a separate post.
Let’s start with the result summary. Synkronizer shows the identified differences categorized by different types, such as “Inserted Columns”, “Inserted Rows”, “Difference in Values and Formulas”, “Difference in calculated Values”, “Difference in Comments” and “Difference in excel names”. These are the differences shown in our example. Synkronizer shows only different types that might be relevant to the chosen comparison type. When comparing databases, additional difference types are shown.
To go to the details, simply click on the number below a different type and it displays all the identified differences of a specific type, including in what cell the difference occurs and the detailed difference.
To see a specific difference in the worksheets, just click on a specific difference and the two worksheets jump simultaneously to the selected difference. Additionally, parallel scrolling makes it very convenient to sift through the worksheets and its differences.
Unfortunately, after highlighting differences most excel comparison tools end. Only very few tools support the user to update and to merge excel files. In Synkronizer you can simply select a difference and click one of the provided options. In other words, with a single click, you can update either a selected difference or an entire different type.
Since there are so many options and features, how to update and merge excel files, I will cover this part in a second post. All I would like to say for now is, that with the right excel compare tool, updating and merging becomes a very easy, simple and fast task.