How to Compare Two Excel Files
Pinterest Stumbleupon Whatsapp
Advertisement

Need to compare two Microsoft Excel files? Here are two easy ways to do so.

There are plenty of reasons why you might need to take one Excel document and compare it to another. This can be a time-consuming task 14 Tips to Save Time in Microsoft Excel 14 Tips to Save Time in Microsoft Excel If Microsoft Excel has consumed a lot of your time in the past, let us show you how to get some of it back. These simple tips are easy to remember. Read More that requires a lot of concentration, but there are ways to make it easier on yourself.

Whether you just need to take a close look manually, or you want Excel to do some of the heavy lifting 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things The power of Microsoft Excel lies in its formulas. Let me show you the wonders you can do with formulas and conditional formatting in three useful examples. Read More on your behalf, here are two straightforward methods to compare multiple sheets.

How to Compare Excel Files

Excel makes it easy for users to put two versions of a document on the screen at once, in order to quickly establish the differences between them:

  1. First, open the workbooks that you need to compare.
  2. Navigate to View > Window > View Side by Side.

Comparing Excel Files by Eye

To get started, open Excel and any workbooks that you’re looking to compare. We can use the same technique to compare sheets that are in the same document 3 Beginner Tips for Managing Worksheets in Excel 3 Beginner Tips for Managing Worksheets in Excel Worksheets are the core essence of Excel. Here are a few tips that'll help you stay organized and productive. Read More or entirely different files.

If more than one sheet comes from the same workbook, you’ll need to separate it beforehand. To do so, navigate to View > Window > New Window.

how to compare two excel sheets side by side

This won’t separate the individual sheets permanently, it simply opens up a new instance of your document.

Next, head to the View tab and find View Side by Side in the Window section.

how to compare two excel sheets side by side

This menu will list all the spreadsheets that are currently open. If you only have two open, they will be selected automatically.

how to compare two excel sheets side by side

Make your selection and click OK. You’ll see both spreadsheets appear on the screen.

how to compare two excel sheets side by side

If it’s more convenient, you can use the Arrange All button to switch between a vertical and a horizontal configuration.

how to compare two excel sheets side by side

One important option to be aware of is the Synchronous Scrolling toggle.

how to compare two excel sheets side by side

Turning this on will ensure that when you scroll one window, the other will move in sync. This is essential if you’re working with a large spreadsheet How to Split a Huge CSV Excel Spreadsheet Into Separate Files How to Split a Huge CSV Excel Spreadsheet Into Separate Files One of Microsoft Excel's shortcomings is the limited size of a spreadsheet. If you need to make your Excel file smaller or split a large CSV file, read on! Read More and you want to continue to check one against the other. If the two sheets become unaligned for any reason, simply click Reset Window Position.

Comparing Excel Files Using Conditional Formatting

In many cases, the best way of comparing two spreadsheets might simply be to put both of them on-screen at once. However, it is possible to automate the process somewhat.

Using conditional formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More , we can make Excel check for any discrepancies between two sheets. This can save a lot of time if all you need to find are the differences between one version and another.

For this method, we’ll need to make sure that the two sheets we’re working with are part of the same workbook. To do so, right-click the name of the sheet you want to transfer and select Move or Copy.

how to compare two excel sheets side by side

Here, you can use the dropdown menu to decide which document it will be inserted into.

how to compare two excel sheets side by side

Select all the cells that are populated in the sheet where you want any differences to be highlighted. A quick way to do this is by clicking the cell in the top-left hand corner and then using the shortcut 3 Essential Time-Saving Excel Shortcuts You Should Know 3 Essential Time-Saving Excel Shortcuts You Should Know Excel can be a headache for the uninitiated, but with these shortcuts in hand, you'll save yourself a ton of time going forward! Read More Ctrl + Shift + End.

Navigate to Home > Styles > Conditional Formatting > New Rule.

how to compare two excel sheets side by side

Select Use a formula to determine which cells to format and enter the following:

=A1<>sheet_name!A1

Just remember to sub out “sheet_name” for whatever the name of the other sheet is. All this formula is doing is checking when a cell in one sheet doesn’t exactly match the corresponding cell in the other sheet, and flagging each instance.

Next, click Format and choose how you want to highlight any discrepancies. I’ve gone for a standard red fill. Next, click OK.

how to compare two excel sheets side by side

Above, you can see the results. Any cells that contain a change have been highlighted red, making it quick and easy to compare the two sheets.

Let Excel Do the Hard Work

The technique above demonstrates one way that you can let Excel handle some grunt work. Even if you’re paying close attention, there’s a chance you might miss a change if you were to perform that task manually. Thanks to conditional formatting, you can ensure that nothing slips through the net 5 Excel Document Settings You Must Never Forget to Check 5 Excel Document Settings You Must Never Forget to Check When you pick an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More .

Excel is good at monotonous and detail-oriented jobs. Once you have a grasp of its capabilities, you can often save yourself some time and effort by using a technique like conditional formatting and a bit of ingenuity.

Do you have a tip about comparing documents in Excel? Or do you need help with the processes described in this guide? Either way, why not join the conversation in the comments section below?

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. JaS
    January 1, 2018 at 11:16 pm

    The eXcel files are "Zipped/XML" files. Change the extension to ".zip" and open it. You will find several XML files describing the spreadsheet. One of them has a detailed entry for each cell. Text compare this with the other eXcel file you changed to a zip.

  2. Andrew
    December 30, 2017 at 2:53 pm

    to compare table of numerical values, edit / copy one. then at the top left of the other, use paste special, difference
    Any place where the two values in the two tables are the same will then show as zero, non zero shows where they are different.

    NOTE - this method is destructive (sort of) - it overwrites the table where you paste, but you can always use undo to reversde that

  3. Flo
    December 28, 2017 at 4:53 pm

    Nice.. I like the Cond. Formatting option.

  4. Phú Ngô
    December 28, 2017 at 2:49 pm

    Hi,
    Another way for compare number
    + Put 2 data compare on 1 sheet, by above and below.
    + Add 1 column beside it with detail for indentify source 1 and source 2, title is source
    + Revert value want to compare of source 1
    + using pivot table
    We see result is column total, total is 0 duplicate, total not 0 is difference.

    PHÚ NGÔ