How to Compare 2 Excel Files (Matches & Differences)

Excel is known for the huge magnitude of data it can store. In single, multiple sheets, and even multiple files.

But how do you compare this data from different Excel sheets and files? Is there any synchronized way to view and compare these sheets and files?

There are. And I have brought together all these methods in the guide that follows 📚

Delve into it and make sure to download the free practice workbook for this guide here to practice all the way along as you read.

Table of Contents

Compare Excel Files by viewing them side by side

One great way to compare any two things is to have them before yourself and compare them as you view them.

To your surprise, Excel offers an in-built View side-by-side feature that allows you to do this 🥽

Pro Tip!

Using these features, ultimately, it’s you who will compare two files all by yourself by critically viewing every minute detail of them.

Hence, this method is only recommended when you have a small dataset in both your Excel files to compare.

As the dataset grows, this method becomes prone to too much time consumption, inaccuracy, and inefficiency.

Two Excel files

If you have two Excel files to compare simultaneously, here’s what to do.

One of my Excel files has a list of students along with their marks 2️⃣

Likewise, in another sheet, I have similar student names and their marks that I want to compare to those in the first file.

To compare both these files by viewing their contents side by side, here’s what we do,

Step 1) Go to the View tab > Windows Group > View Side by Side button.

This will squeeze both the sheets to fit your screen simultaneously like here.

However, in most of the types, a horizontal arrangement of both files is not easily comparable.

Step 2) Go to the View tab > Windows Group > Arrange All button.

From the Arrange Windows dialog box, select the arrangement of how you want the open Excel books to be viewed. I am selecting a Vertical arrangement 📝

Both your Excel workbooks will appear side by side.

Pro Tip!

As soon as you select View Side by Side mode, Synchronous Scrolling is automatically enabled. However, if it is not already enabled, go to the View tab > Windows Group > Synchronous Scrolling button.

Now as you scroll down one Excel file, the other one will also be scrolled. Technically, you can control both books with one scroll bar.

More than Two Excel files

If you have more than two Excel files, to compare them by viewing 👀

Step 1) Open all the to-be-compared workbooks simultaneously.

Step 2) In any one of these workbooks, go to the View tab > Windows Group > View Side By Side button.

Step 3) From the Compare Side by Side window, select any Excel file.

Step 4) Go to the View tab > Windows Group > Arrange All button.

Step 5) Choose how you want to compare the files (tiled might be a good option for multiple workbooks).

There you go!

This method allows multiple workbook comparison in a seamless manner.

Compare two sheets from the same Excel file

Apart from different workbooks, sometimes all you want to do is compare two Excel sheets (or more) from the same workbook 📃

To do so:

Step 1) Go to the View tab > Window group > New Window button.

Excel will make a copy of this workbook for you and launch it as a new window.

Here onwards the steps are the same as for comparing two workbooks (because you now have two workbooks open).

Step 2) Go to the View tab in any of the books > Window group > View Side by Side button.

Step 3) Select the first sheet in the first book and the second sheet in the second book.

Scroll in a synchronized manner and compare both books conveniently by viewing them.

Using the IF function

The IF function is a game changer in Excel, and I just love it for reasons I am going to explain in this section.

If you have data spread across two sheets (or even two workbooks) and you only want to compare them to find out the differences, you probably don’t need to invest so much time viewing these sheets side by side 🚴‍♀️

Just write up a formula and let Excel find out the differences for you like a pro.

For example, I have a dataset in Sheet 1 here.

And a copy of this data (apparently) in Sheet2 here.

However, I suspect some differences in both these datasets. To quickly compare these datasets from both these sheets to find these differences:🔍

Step 1) Launch a new sheet.

Step 2) Activate a cell and write the IF function as follows:

Step 3) Write the logical test that checks if cell A1 of Sheet1 is equal to A1 of Sheet2.

To refer to these cells from other sheets, just keep the formula open and go to the to-be-referred cell from the relevant sheet. Excel will automatically pick out its reference.

Or you can manually type it as SheetName + ! + Cell Reference.

Check out this blog to learn more about How to Reference Tab & Sheet Names in Excel.

Step 4) Write the value_if_true as an empty string (“”).

Step 5) As the value_if_false, write the following string:

• The first component is a text string (Sheet1: ) enclosed in double quotation marks.
• Then an ampersand (&) is used to connect this text string to the value in Cell A1 of Sheet 1 (Sheet1!A1).
• Again, an ampersand (&) and the text string ( | ) to create a comparison wall between the cell values from both the sheets 🏃‍♀️
• An ampersand & and then the text string (Sheet2: ) connected with the cell value from Cell A1 of Sheet 2 (Sheet2!A1) using an &.

This makes the IF function look like below:

All in all, this makes Excel check if the value in cell A1 of Sheet1 is equal to the value of Cell A1 in Sheet 2. If yes, good, you get an empty text string.

If not, it will return the value from Cell A1 of Sheet 1 compared to the value of Cell A1 of Sheet 2.

Step 6) Press Enter.

Step 7) Drag this formula down and towards the right in line with the dimensions of the dataset in both your sheets (our is 5 columns and 4 rows) so I am stretching this formula accordingly.

Tada! For all those cells where the values in both the sheets differ, Excel presents you with a comparison of how both these sheets differ 📽

For example, for Cell B1, Excel says that this cell from both the sheets is different as this cell from Sheet1 shows Blue and the same cell from Sheet2 shows Ball.

Since we have used an absolute reference for Cell A1, as we drag the formula around the cells, the cell reference updates, and all the cells from the dataset are compared.

How cool is that!

Using conditional formatting

Conditional formatting is used to apply formatting to cells in Excel based on a specified condition.

This can be used to compare two sheets to spot the differences between them. I love how this method smartly picks out the differences between two sheets in a blink 🎨

I have this data in the first sheet of my Excel file (it contains the personal details of a couple of people).

And then in the next sheet, I have the same date (apparently) but there are some differences in both these sheets that are not evident just by looking like that.

Let’s use conditional formatting to find out these differences.

Step 1) Go to the sheet where you want the differences highlighted.

Step 2) Select the cells that you want to be compared.

Step 3) Go to the Home tab > Styles Group > Conditional Formatting > New Rule.

Step 4) In the New Rule window, write the following rule:

Pro Tip!

While writing a rule in conditional formatting, as you select a cell from your sheet, Excel automatically uses its reference in the rule as an absolute reference.

You need to manually adjust it to become a relative reference or else only that one cell (say $A$1) will be compared to cell $A$1 of the other sheet. And the rule would fail.

Step 5) Set the format based on how you want the different cells highlighted.

Step 6) Press Okay.

Instantly, all the cells from the active sheet that are different from the other sheet will be highlighted. So, you instantly know how both the sheets differ 🤯

Pro Tip!

We have seen multiple methods of how you can compare two sheets and instantly find how the cells in these sheets differ.

However, it is important to know that both, the IF function and Conditional formatting method have some limitations. For example, they can tell you if Cell A1 of one sheet is different from Cell A1 of another sheet.

But maybe it’s just that the value in Cell A1 of Sheet1 is written in Cell B1 of Sheet2 instead of A1. So, the data is the same, it is only in different cells of the cell range 💡

Such differences won’t be identified by these formulas. Similarly, they won’t identify differences in cell formatting or formulas. They also cannot cater to the addition or deletion of rows or columns in either of the sheets.

Conclusion

In the tutorial above, we have seen many methods of how you can compare two sheets from the same Excel file and different Excel files. And not only two but even more.

Comparing Excel sheets and files is an excellent and very useful tool of Microsoft Excel. Helps you scan two files in a single go and extract the differences in a snap. We love it and hope you do, too. Additionally, it is recommended that you explore other similar tools of Excel explained in my following Excel tutorials.

• How to Compare Two Columns in Excel: Step-by-Step
• How to Open Excel in Safe Mode Step-by-Step (+ Shortcut)