Compare Two Sheets in Excel from A to Z

What is Compare Two Sheets

In this video, I will show how to use the Compare Two Sheets tool for Excel. It is part of Ablebits Ultimate Suite.
The general idea of the tool is to find and color differences between the two sheets. The Compare Sheets processes your data row by row searching for different cells in the same or nearest following lines.

To run the tool, go to the Ablebits Data tab, find the Merge group and the Compare Two Sheets button.
In the first window, you will see limitations and requirements.

Your sheets should not be protected and must be saved.

The sheets you compare should have the same layout and structure which includes grouping, filtering, and sorting.

The supported formats are xlsx and xlsm.

And last but not least: The utility won’t compare sheets that contain merged cells.

If your sheets correspond to all these requirements, click Agree, and let’s move forward.

Step 1: Here we select the worksheets and ranges. In the list of open books, choose the sheets you are going to compare. If the needed workbook is not here, you can open it by clicking the Open workbook button.

By default, the tool compares the used ranges of the sheets. If you need to compare other ranges, click the Select range icon right here and choose the areas of interest. And click Next.

On the second step, you select the comparing mode and the matching option. Here I need your undivided attention, first it may seem complicated, but only till you get the working logic of the tool.

Comparing modes: No Key Columns, By Key Columns, Cell-by-cell

We have developed three comparing modes for three types of Excel sheets.

The No key columns mode suits to sheet-based documents like invoices. These sheets may have a different number of rows or include several tables.

You need the By key columns type if you have column-organized tables that contain row-by-row data. For example, it is perfect for two price lists with the same "SKU" or "Product ID" columns. Usually, these columns are called "key columns", this is why we named this comparing mode By key columns. Such sheets always have the same number of columns, but they may include a different number of lines.

The Cell-by-cell mode is designed for documents with the same layout and size, like balance sheets or month-to-month reports. In this case, there can’t be any key columns, they simply do not make any sense.
By the way, I have made videos about each comparing mode, you can find the links in the description to this video and on our docs page.

What are the key columns

So, now I select By key columns and my key columns will be Fruit and Package. Checking line by line, Compare Sheets will search for differences, looking for pairs of similar rows according to the values in the Fruit and Package columns. But there are cases when one row in Sheet 1 may correspond to several rows in Sheet 2 with full or partial cell match. For such situations, Compare Sheets provides three matching options. Please note that in the following examples all the three columns are the key ones.

Matching options explained

The First match means that the first row that has the same value in the first key column will be considered as the matching one. One and the first match is enough, the tool finds it and colors all the next rows as unique. It doesn't search for any more matches in other key columns.

The Best match means that Compare Two Sheets will search for the highest number of the same values in all the key columns. The row with the maximum number of matching values will be considered as the matching row. Other rows will be colored as unique.

And the easiest to understand matching mode is the Full match only. The tool will scan all the values in the key columns and if there are no full matches, it will highlight all the rows as unique ones.

I pick the Best match and click Next.

Step 3: Select the key columns
If you choose the By key columns mode, Compare Sheets will ask you to select the key columns. Simply pick them for Sheet 1 and the add-in will automatically find the corresponding columns in Sheet 2.

Additional options for your comparison

Step 4: Choose the comparison options
By default, Compare Sheets searches for differences in values only, but you can select other options as well:

Different formulas can return the same values, therefore by default differences in formulas are ignored. But if they are important for you, check this option.

Also, you can make the tool color cells that have differences in formatting by ticking this box.

By default, we ignore hidden rows and columns. If you need to find differences in hidden rows or columns, uncheck this box. But of course, you will not see these differences marked if you don't make them visible at first.

Sometimes empty rows and columns are an important part of our data. If this is your case, untick this checkbox and they will be included in the comparison.

If you pick this option, Compare Sheets will add a new column to your sheets and mark the found differences there.

Besides, you can choose not to do anything with your cells, but instead, mark all the differences in a special column. Tick this option and the differences will be identified only in the status column. Your tables will remain intact.

Also, you can make the tool ignore leading and trailing spaces. If you do not check this box, two cells that contain the same value, but only one of them, for instance, also has some spaces before or after the value, will be considered as different. If your data set may include such extra spaces and they do not represent any additional meanings, better tick this box to make the comparison more accurate.

Select the marking option that suits you the best. By default, we suggest using a background color. But if you need to see the original color of your cells, you can mark differences with a font color or bottom border.

Please note that if your cells already have background coloring and you pick background color here, you may lose the original color of your cells in the compare differences mode. However, if you pick the Ignore differences option, the original color will be restored.

And click Compare.

What is Review Differences Mode?

When the tool finishes the comparison of the sheets, it will open them side by side in the special Review Differences mode. What is so special about it?

The basic idea behind this mode was to develop an effective way for you to navigate between the found differences and review then in place. The selection of the found different lines is synchronized in both sheets so you could see them side by side and decide which one is more correct or up-to-date, which one you want to leave or remove. To make it work like this, it is crucial to preserve the original layout and structure of your sheets in this mode.

For these reasons, in the mode, we have disabled the Filter and Sort options. So please, don’t be surprised.
The same goes for insertion and removal of rows and columns: you can’t do this in this mode. But when you finish processing the found differences, you can quit the mode and do with your data set whatever you need: extract rows, add or remove rows and columns, etc.

Why cells and rows are colored

So, in our sheets, we see blue and coral rows and some green cells. Let’s discuss why they are colored.
The blue row in Sheet 1 means that it is unique, not duplicated in Sheet 2. Why has the tool decided so? Because of the key columns! It hasn’t found any match to Grapefruit in Sheet 2. The same goes for the coral Avocado row in the second sheet: as I have selected the best match option, Compare Sheets has identified the row number six as matching – because it has both Avocado and Tray – and row number five as unique. If I picked the First match option, this would be the other way around.

Next, what do we have here with green cells? As the first lines have Mango and Tray in the key columns, they are considered as matching. The cells that are different in the matching rows are colored in green.

But why are these two cells with the same values are green? Look, the key columns share the same values – Banana & Tray – so we are dealing here with matching rows. These two cells are deceptively the same, but if you remember the additional options we checked, you’ll find out that they are colored because we asked the tool to consider differences in formulas as well. And here we have exactly this case: the formulas are different!

And in the same row, we have another mystery: the D5 and D4 cells. Any idea why they are colored? Again, let’s recall the additional options: we checked Show differences in formatting, right? What are the formats of these cells? As you can see, the first one is formatted as text, and the second as number, this explains everything. Voila, now we can be sure there is no mistake.

These two cases illustrate the importance of being very careful while selecting additional options. In 90% of cases when users text us that the tool works incorrectly, we find the reason there – in the selected options. It’s always better to take a minute and thoroughly think about the additional options to check or untick. They directly affect the result of the comparison.

How to use the toolbar

You probably have already noticed that each window has a vertical toolbar with several buttons. In the inactive window, the toolbar is disabled and decolorized to make you focused on the active worksheet.

To leave the values as they are and delete the coloring use the Ignore button right here!

If you need to replace a value, select the cell that contains the value you want to paste to another sheet and click this arrow button. If you’ve done something wrong with the toolbar, take advantage of the UNDO button.

Also, you can replace several cells, selecting then with the help of the Ctrl key. You can transfer values from both Sheet 1 and Sheet 2.

Besides, feel free to paste entire unique rows. See? There it is! In this sheet, it has lost its background color as it is not unique anymore.

Or maybe your task is to delete values in both different cells? Use the Delete button!

Also, you can delete the entire unique row.

Please be very careful while processing your data in the Review Differences mode. If you start using the toolbar and then delete something from your table, neither the Undo button on the toolbar nor the Excel's Undo can recover the deleted information.

How to exit the Review Differences mode

When you are finished reviewing the differences, you can exit the mode and get back to the normal Excel’s view.

Click the Exit button right here and you will see three options.

You can save your books – meaning, save all the changes you’ve made in the mode – and whether to keep or remove the coloring made by the add-in. For this, select the first or the second option.

I picked the first option and here are my sheets: the coloring is kept, and the changes I made are saved.

With the help of the Restore workbooks from the backup copies command, you will just get your books back to the condition in which they were before working with Compare Sheets.

I really hope this tutorial was helpful. If you still have questions or doubts about Compare Two Sheets, you are very welcome to share them in the comments area or by contacting us by email: support@ablebits.com.
Thank you.