Compare Excel files by all columns

Video transcript

In this video, I will show how to find differences in two Excel invoices. I will use the Ablebits Compare Two Sheets tool and its No key columns comparing mode.

Let's find the Ablebits Data tab in the ribbon. We need the Merge group and the Compare Two Sheets tool.

At the very beginning, the tool will show you the requirements and limitations. In brief, the sheets you compare should be saved and should not be protected. Please make sure they do not contain merged cells. The worksheets should have the same structure, including groping, filtering, and sorting. This is important to avoid comparing, for instance, header rows with regular table rows. We click Agree and here is the first step.

We should select the sheets. Your invoices may be in the same Excel book or in different files.

On this step, let's pick the comparing mode and the matching option. The tool offers you three comparing modes for different types of sheets. For invoices, I choose No key columns.

In fact, this mode is suitable for comparing not only invoices but any other sheets that may include several differently organized tables.

And here we should select the matching option. The result you will get depends on the matching option you select, so it is crucial to understand these options.

Please pay attention to this note: For partially matching rows. The point is that the matching options work for only lines that are not identical in your sheets. In such cases, for a row in the first worksheet, the tool may find several possible matching rows in the second sheet.

Here we have the schemes that illustrate each option.

If you choose First match, the tool will take the value in the first column of your first sheet and match this row to the first found line that has the same value in the second sheet. It will not look for any other matches in other columns of these rows. If there are other rows in Sheet 2 that contain the same value, they will be marked as unique.

If you pick Best match, the utility will scan the entire second worksheet for the highest number of the same values in the row. The row that has the maximum number of the same values will be considered matching, and the cells that are different will be colored. Other rows with fewer matches will be identified as unique.

And the name of the Full match only option speaks for itself. If you select it, there won't be any partially matching rows - either fully matching lines, or unique ones.

I pick First match. Let's see what it will show us!

And here we can select the additional options and the way to mark differences.

I pick Mark differences with background color.

The different cells and lines will be highlighted with the following colors:
Blue is the color for rows that exist only in Sheet 1.
Unique rows in Sheet 2 will be highlighted with coral.
In the matching rows, different cells will be colored in green.

You are free to select any other colors! Let's click Compare.

Now our sheets are placed side by side in the review differences mode. Here is the toolbar that lets us perform various actions with the found differences.

So, there are no differences in the address, but there are some green cells in the right corner. Why are these cells colored? They look identical, but here I have a misprint, letter I is missing in the Invoice word.

I can easily correct it simply copying the cell value from Sheet 1 by clicking the Copy button.

The invoices have different numbers and different dates. These rows are matching because the values in the first columns are the same. The different cells in the matching rows are colored in green.

Next, here we have an almost empty row except for the values in the B9 cell. Again, it is colored in green, because empty cells are matching, making the entire rows matching as well.

Look, here is the row colored in blue, that means that it is unique! The row is marked as unique, because it is blank, while the same row number 14 in the second table has plenty of values. No matches make the row unique.

And here is the interesting part. The first sheet contains two items, the second sheet - three, but one of them is duplicated. No problem with item 1 - the price, quantity, and subtotal are the same. But the result with Item 2 may seem confusing.

The second row in the second invoice has more in common with the same row in the first sheet: the name and the price. But the tool has decided, that the matching row is the sixteenth one, and the seventeenth is colored as unique. Why? That's right because we picked the First match option! Compare Two Sheets has found Item 2 in row 16 in the second sheet and stopped the search!

And here we have green cells because in these cells the entries match, and the rows are considered matching.

To cancel the review differences mode and remove difference marks, I click here.

Let's go through the same steps again, but this time choose Best match. Do you think the result will differ?

Ablebits Data - Compare Two Sheets - Agree - Next - No key columns - Best match - Compare!

The coloring is almost the same as in the previous comparison, despite these two rows. Do you remember: the previous time the matching row was the sixteenth one, and the seventeenth was colored as unique. Now it is the other way around: the unique is the sixteenth row, and the matching is the seventeenth.

As you have probably guessed, the reason for this change is the matching mode we selected. In the Best match mode, the tool has found the maximum number of matching values - Item 2 and twelve dollars - and took this row as the matching one.

And now we will try out the third matching option: Full match only. What picture will we get, do you have any idea?

Look, now only two colors are used: blue and coral. So the picture is totally different! If you select Full match only, all rows that have even one different cell, are marked as unique. So, no green cells can be here.

I hope this video has helped you understand the Compare Two Sheets tool better and select the correct options for your comparison needs.

If you have any questions or issues with this tool, please do not hesitate to contact us right away.