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.

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️⃣

Students and their marks in Excel spreadsheet

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

Second Excel worksheet

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.

View side-by-side button

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

Sheets opened simultaneously

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.

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 πŸ“

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.

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.

Compare Side by Side

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).

Desired arrangement

There you go!

Multiple workbooks arranged

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.

New Window button for spreadsheet compare

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.

If you have multiple sheets that you want to compare, create as many windows as many sheets you have (by re-clicking on the New Window button). Once you have the required number of windows launched, follow the same steps as explained above to compare multiple workbooks.

Kasper Langmann, co-founder of Spreadsheeto

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.

Data in Sheet1

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

Data in Sheet2

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:

Click to copy

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

Click to copy
IF logical condition

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 (β€œβ€).

Click to copy
value if true

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

Click to copy
  • 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 &.

The text strings like a vertical bar (|) that I have used in the formula above are discretionary. You can replace them with anything of your liking. Or if you do not want to see how both the values differ, you can simply replace the value_if_false with something simple like “Doesn’t Match” or “Different values” etc.

Kasper Langmann, co-founder of Spreadsheeto

This makes the IF function look like below:

Click to copy
value if false

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.

Find differences in two sheets with the IF function

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.

Dragging formula across the dataset dimension

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).

Data of employees is xlsx file

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.

Data of employees in the second sheet

The first sheet containing this data is named CF – Data I and the second sheet to which it is being compared is named CF – Data II.

Kasper Langmann, co-founder of Spreadsheeto

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.

If you want to compare the whole sheet, select any cell of the sheet and press the Control key + A to select the whole sheet.

Kasper Langmann, co-founder of Spreadsheeto

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

Conditional formatting button

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

Click to copy
Conditional formatting rule to compare files

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.

setting up the format to highlight differences

Step 6) Press Okay.

Run the conditional formatting rule

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.