How to Compare Two Excel Sheets?

If you need to compare data from two Excel spreadsheets and you don’t know how to do it, then you’re in the right place. In this article, we will show you how to compare two Excel sheets based on values or formatting.

Things to know

There are a variety of methods to compare two excel sheets on the same or different sheets. The method you choose will depend on how your data is structured and what kind of comparison you’re looking for.

Let’s get started!

Method 1: Compare two Excel sheets (View Side by Side)

To compare two Excel sheets side by side, follow the below-given steps –

Open the Microsoft Excel sheets that you want to compare.
Click on the View tab and then click on the View Side by Side option in the Window section.

In the view tab, click on the Arrange All button under the Window section.

Select Vertical in the “Arrange” window and select OK.

Note: If you want to scroll through both worksheets simultaneously to compare data row-by-row, make sure the Synchronous Scrolling option it turned on.

Method 2: Compare two excel sheets using Formulas

Create a new worksheet within the same workbook that contains the worksheets you want to compare.
The formula to use is =IF(‘Ver1′!A1=’Ver2′!A1,’Ver2′!A1,”Ver1:”&’Ver1′!A1&” | Ver2:”&’Ver2’!A1)
Copy the formula across and down the worksheet.

Method 3: Compare two excel sheets using Conditional Formatting

Go to the Home tab on the ribbon and click on the Conditional Formatting button in the Styles section.
Select New Rule in the menu. In the options that show up, click on ‘New Rule‘.

In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
Click the Format button.
Click on the ‘Fill tab’ and select the color in which you want to highlight the mismatched data.
Click on the OK button at the bottom of the New Formatting rule dialog box.

Method 4: Compare two excel sheets using XL Comparator

XL Comparator is a free, web-based, online, simple, as well as easy-to-use tool. There are the following steps to compare two Excel sheets using XL Comparator

Open https://www.xlcomparator.net/ on your web browser.
Use the Choose file option to upload two files (the maximum size of each file can be 5MB).
Click on the Open button to upload the excel sheet.
After uploading both excel sheets, click on the Next button.
On the left side, click on the File Columns drop-down menu and select the column that you want to compare.
Click on the Next button at the bottom of the screen.
Download the comparison file which will have the data (based on what option you selected)