Synkronizer - your easy way to compare Excel files

In this article you'll see how to compare two Excel files with the help of the Synkronizer add-in.

As using Excel worksheets to store data grows exponentially, the demands on having a tool that allows to compare Excel files and update them effectively are increasing. There is a need for customizable software that lets you easily select any number of worksheets, find and visualize the differences, and allows maximum automatization.

If you want to compare several versions of a workbook in Excel, analyze them for issues or inconsistences, or see what kind of changes have been made, try the Synkronizer add-in. It matches your files, lets you see the differences cell by cell, and update your tables lightning fast. The results are color coded by the kind of content, values, formulas, named ranges, formats, columns and rows, duplicates and comments. In addition, Synkronizer creates a report showing detailed information about the workbook and its structure.

Easily select workbooks and worksheets to compare in Excel

You can run the Synkronizer add-in by clicking on its icon under the Add-ins tab in Excel.
Run Synkronizer by clicking on its icon under the Add-ins tab

The program appears as a pane next to your workbook. You can dock it left or right, use as a floating form, make it wider or narrower. When you open the add-in, the Synkronizer pane shows a ribbon with 3 tabs: Project, Results and Select. The last one is active by default.
The Synkronizer pane appears next to your workbook

First off, it's necessary to choose the source and target workbooks, which is easy with the drop-down lists that contain all recently opened items. Or click on the special icon to browse for the needed name. As soon as you pick the Excel files to compare, they are placed side by side so you easily see all the differences. Synkronizer works with all types of files supported by Microsoft Excel.

On step two you select the source and target worksheets. It's possible to include protected, empty or hidden sheets, match them by name, index or code name. You can specify how the worksheets should be paired - automatically pick all or only the first sheets with the same name, or choose them manually. Just click on the icon with a double arrow to add all matching worksheets automatically.
On step two you select the source and target worksheets

You will see all sheets that suite your criteria on the Synkronizer pane. In addition, you can press the Clear list button and remove the selected items, as well as specify certain ranges if you don't want to compare entire sheets.
See all worksheets that suite your criteria on the Synkronizer pane

Step 3 offers to choose among the following Excel compare options.

  • Compare as worksheet. To use this option, please make sure that your data tables have the same number of rows and columns which have the same location in both sheets. Also your worksheets mustn't contain any new or deleted rows or columns. If this is the case, your files will be compared using the "1 on 1" method which means that the add-in will detect only cell differences.
  • Compare with link options. Compare the rows and/or columns by linking cells, columns and rows 1 on 1.
  • Excel compare as database. If you choose this radio button, your worksheets must have a database structure. This means they need headers and one or more key columns with unique identifiers to be used for matching. This option allows to find duplicates and merge or consolidate your worksheets.

Step 3 offers to choose among the following Excel compare options

Choosing to compare Excel files as database, you will get a new tab with settings. It allows you to select one or more columns with key values that will be used for comparison. There is also an option to define if the headers consist of one or more rows. It's really helpful if you have a hierarchy of header rows.
Configure database settings

The following additional options let you fine-tune the search for the database setting.

  • Relaxed keys. By default the add-in uses case-sensitive matching. Thus it will see John and JOHN as two different values. Excess spaces may also be an issue. Choose the Relaxed keys checkbox to ignore the differences in spaces and upper/lower case.
  • Sort records. The records will be sorted by key values.
  • Group records. The data are grouped by linked records, new or deleted values, additional matches, and duplicate rows.
  • Add SynkID column. Insert a special column that will show the status of the records in your worksheets. You will easily spot duplicates, missing rows and records that are different in two tables.

Having selected the worksheets to match, let's look at the ribbon under the Select tab to define additional Excel compare settings.

Additional options to compare data in Excel

Under the Select tab, you can find the Compare and Tools groups with additional options that let you compare worksheets in Excel exactly as you want.

If your tables contain comments or named ranges, you can find any changes and unify the sheets using the Content menu.
Choose the content settings

You can compare the following cell formats between the sheets: number, alignment, font, border, fill, and protection.
Choose the formats settings
The filtering function enables you to reduce the number of differences that will be found and displayed. Just click Enable filters and choose the filter(s) which you want to apply.

  • When you compare two files in Excel, you can choose to ignore case, spaces, different format of identical numbers, constants, formulas, hidden columns and rows. If your tables contain different formulas that result in the same value, you can choose to see such results as different.
  • It's possible to choose a deviation to show how tightly the values in the dataset must be bunched around the mean value. For example, if you enter 0.5, then only deviations greater than 0.5 will be shown.
  • One more really helpful feature is defining equivalent values. Suppose, you have 2 worksheets with survey results. In one sheet yes is expressed by the plus sign and in the other - by the word true. You can specify that + is the same as true and have your Excel worksheets compared in an instant.

Choose the filters settings

If necessary, you can automatically generate a repot with all the differences found. You can even choose a hyperlinked report where each difference has a link to the corresponding difference in your table.
Choose to generate a comparison report

In the Highlight menu you can define if you want the differences to be marked in your worksheets.
Choose to highlight the found differences

The Outline option lets you specify if you need to see only certain types of differences. Just a click to display missing columns, missing rows, duplicate, identical, or different rows.
Specify if you need see only certain types of differences

Display and analyze the differences on the Synkronizer pane

After clicking the Start button you will automatically move to the Results tab where you can easily spot all the differences. The summary report with all the changes will be generated in a separate Excel file.
Automatically generate a difference report

Synkronizer automatically displays the corresponding areas of both worksheets side by side. In the upper part of the add-in pane you'll find the worksheets and the number of differences found. The lower part contains changes for the selected worksheet and category. With this pane you can efficiently review and manage the differences, navigate to each one and decide if it needs to be transferred. Feel free to copy values from the source sheet to the target table and vice versa.
See and analyze the results on the add-in pane

The differences like missing columns and rows, formulas, comments, named ranges and other are displayed in the columns of the list box. Below you can see the changes found between my worksheets.

  • Some of the columns and rows exist only in the target or source sheets.
  • There are also duplicate records that should be deleted or processed further.
  • The add-in has found differences in values ​​and formulas.
  • Some of the tables contain different cell formats and missing comments.

See the differences color coded

If you click on each difference, you'll get the details displayed in the lower part of the form.
Process the differences on the Synkronizer pane

Click on the difference value to see the list of dissimilarities on the pane and easily navigate to them. Here you can also insert or delete the selected differences. If I select the Columns differences value, I can clearly see that my source table is missing two columns that my target table contains. I can use special buttons to insert just one or both columns to my source sheet, or delete the columns from the target list. Once the columns or rows have been copied, they are displayed in red.
Choose what to do with the found differences

Just alike we can transfer or delete duplicates, update our sheets with formulas and comments, fix incorrect formatting.

Each feature has its sorting, grouping options and settings that let you customize comparing and updating your Excel files further.

  • Sort by row. By default the changes are sorted by rows and then by columns.
  • Sort by column. The differences are sorted by columns and then by rows.
  • Group by row. Use this option to group the differences by rows. The differences can be transferred individually per line or in groups.
  • Group by column. If you choose this option, the differences will be grouped by columns. The differences can be transferred individually per line or in groups.

For missing columns and rows you can choose to overwrite data with empty cells, and remove the initial value. If you want to re-compare the files, click on the Refresh icon.

Create, save and manage Excel compare projects with ease

To avoid selecting files and worksheets, and changing the settings again and again, you can save any comparison process as a separate project. Just click on the Project tab to create a new project, open, save or reload it. Here you can also see the list of recently opened entries and configure settings for each saved file. In addition it's possible to load, save and reset your preferred settings like contents, formats, filters, report, highlighting and outline.

The option to highlight the differences with color is great. You can change the standard color scheme and choose your own theme to display the changes in your worksheets. The colors can be reset to the original if necessary.
Configure the project settings

Last but not least is the option to choose among 12 languages for the most convenient work with the add-in.

Use Synkronizer whether you are new to Excel or an Excel guru

Whether you are an Excel guru who loves coding custom functions or you're simply looking for a better way to compare Excel files, you may find Synkronizer extremely helpful.

  • Compare and update your Excel files in an instant without complex formulas or VBA.
  • Benefit from the filter settings and user-friendly customization menus.
  • Solve more complex tasks and requirements using command lines and VBA offer and powerful comparing scripts.

Feel free to download and install the Synkronizer add-in to see how it can help you compare two Excel files.

Free download version Synkronizer for Excel

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.