How to compare two Excel sheet

It is important for us to compare two Excel sheets when a lot of users are making changes to the same Excel sheet. This helps us to identify problems associated with sheets such as duplicate records, broken links, inconsistent formulas, as well as wrong formatting. Excel sheets become difficult to compare when a lot of data needs to be compared.

Note: Microsoft Excel also allows us to compare two different Excel sheets and merge them into a single sheet.

There are a variety of methods to compare two excel sheets in the same or different sheet. You can choose one of them based on your requirement as to what comparison method appropriate based on the data –

1. Compare two Excel sheet using View Side by Side button
2. Compare two excel sheet using the online platform
3. Compare two Excel sheet using formulas
4. Compare two excel sheet using conditional formatting
5. Compare two excel sheet using a third-party tool – XL Comparator

Method 1: Compare two Excel sheet using View Side by Side button

View Side by Side button in an in-built Excel feature used to compare two excel sheets. It is the simplest, quickest, less time consuming, and easiest method to compare small size excel sheets by Eyes. To compare Excel sheets, we use View Side by Side button under the View tab on the Ribbon.

Note: This method is recommended only when you have a small dataset present in your Excel sheet.

Steps to compare two excel sheet

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

Step 1: Open the new or the existing Microsoft Excel sheets that you want to compare.

Note: To open a new Microsoft Excel sheet, type Microsoft Excel at the bottom of the Search bar. (for Windows 2010)

Step 2: Enter data in the Excel sheet based on your requirement.

Step 3: Go to File -> New to open a new blank Excel sheet for the comparison.

Note 1: You can also press Ctrl + N keys from the keyboard to open a new blank sheet.

Note 2: To open an existing sheet, Go to the File tab and select Open from the list. Browse the sheet that you want to compare and click on the OK button.

Step 4: Click on the View tab on the Ribbon and then click on the View Side by Side option in the Window section.

Note: You can also click the New Window option to open a similar Excel document for comparison.

Step 5: Now, you can see that both sheets are displayed horizontally for the comparison.

Step 6: To view both sheets vertically (Side by Side), click on the Arrange All button in the Window section. A small Arrange Window dialog box will appear on the screen in which click on the radio button associated with the Vertical and click on the OK button, as shown in the screenshot given below.

Now, you can see that both sheets appear vertically (Side by Side) on the screen.

Note: This method automatically enables the Synchronous Scrolling option. Now, you can scroll both sheets simultaneously to compared data row by row or column by column.

Compare multiple excel sheets side by side using the View side by side button

Microsoft Excel 2010 and above versions allow us to compare more than two Excel sheets simultaneously using the View Side by Side button in the Window section.

Follow the below given easiest steps to compare multiple Excel sheets using View Side by Side –

Step 1: Open a Microsoft Excel worksheet that you want to compare.

Step 2: Create multiple Excel sheets that you want to compare with the existing document.

1. To open a blank sheet, press the Ctrl + N key from the keyboard.
2. To open your desired document, Go to File – > Open -> Browse desired file location -> click on the Open button.
3. To compare with an existing sheet, go to the View tab on the ribbon and click on the New Window option in the Window section.

Now, you can see that all sheets will appear at the bottom of the screen.

Step 3: Go to the View tab on the Ribbon and click on the View Side by Side option in the Windows section.

Step 4: Click on the Arrange All button under the Window section. A small Arrange Windows dialog box will appear. Click on the Tiled radio button under the Arrange section and click on the OK button.

As a result, you can see that multiple Excel sheets appear side by side.

Method 2: Compare two Excel sheets using the online platform

Microsoft Excel allows us to compare two Excel sheets using the online platform. This method helps you to easily search and highlight the difference between the two Microsoft Excel sheets. The main disadvantage of this method is that it requires a high-speed internet connection to compare the excel sheets.

Note: In our case, we use the cloudyexcel website to compare the Excel sheets.

Steps to compare two excel sheets

Follow the below-given step by step procedure to compare two excel sheet using the cloudyexcel –

Step 1: Open any web browser. Type or copy-paste the following link on the browser. You can simply click on the below link to open cloudyexcel website. https://www.cloudyexcel.com/compare-excel/

Step 2: A cloudyexcel official website will appear on the screen. Click on the Upload File 1 button to upload the first excel sheet for comparison.

Step 3: Browse the Excel sheet that you want to compare and click on the Open button to upload the sheet.

Step 4: Click on the Upload File 2 button to upload the second Excel sheet, as shown in the screenshot given below.

Note: Follow step 3 again.

Step 5: Once both Excel sheets uploaded successfully. Click on the Find Difference!!! button.

The screenshot below shows the comparisons between two Excel sheets.

Note: cloudyexcel allows us to easily and quickly compare more than two Microsoft Excel sheets.

Method 3: Compare two Excel sheet using the formula

This is the quickest method to compare Excel Sheet’s data and identify the differences between Sheets.

Note: In this method, you need a separate Excel sheet to view the differences.

Steps to compare two Excel Sheets

Follow the below-given steps to compare two Excel sheets –

Step 1: Open a new Microsoft Excel document by double-clicking on the Microsoft Excel icon.

Step 2: Enter data in the Excel sheet that you want to compare. Save the sheet with your desired file name (In our case, we use the first file name as First.xlsx)

Note: To save sheet, go to File -> Save as -> Browse file location -> click Save button.

Similarly, open a second excel document, enter data on it, and save it as Second.xlsx.

Step 3: Create or Open a new Excel sheet and saved it with the file name Compare.xlsx.

Step 4: Type formula IF(First!A!<>Second!A1,”First Value:”&First!1&CHAR(10)&”Second Value:”&Second!A1,””) in the cell A1 on sheet 3 and press the Enter key.Note: In the above formula, the IF condition is used to check the cell differences in both sheets.Step 5: A small dialog box will appear on the screen. Select the sheets that you want to compare and press the OK button.

Method 4: Compare two excel sheets using Conditional formatting

Conditional formatting is an in-built Microsoft excel feature that helps us to quickly analyze (highlight) the differences between two excel sheets. In this method, you can compare two sheets within the same excel workbook.

Note: It is not a good method when you have a lot of data to compare.

Steps to compare two excel sheets

A list of steps to compare two excel sheets is discussed below –

Step 1: Open a Microsoft Excel document by double-clicking the Microsoft Excel icon on the desktop.

Step 2: Prepare a list of data for the comparison. Once you entered data, click on Sheet 2 at the bottom of the current excel sheet, as shown in the below screenshot.

Step 3: In the second sheet, enter the data that you want to compare with the previous sheet.

Step 4: Select or Highlight data in the sheet on which you want to see the changes.

Note: In our case, we select price cells in sheet 2.

Step 5: Go to the Home tab on the ribbon and click on the Conditional Formatting button in the Styles section.

Step 6: A list of options will appear. Click on the New Rule.

Step 7: A small New Formatting Rule dialog box will appear on the screen in which do the following –

1. Click on the Use a formula to determine which cells to format option under the select a rule type: section.
2. Type formula =B2 <>First!B2 in the Format values where this formula is true section.

Note: In our case, B2 is the cell from where we select our data. First is the sheet that we want to compare with.

3. Click on the Format button to specify the formatting for the differences as shown in the below-given screenshot.

Step 8: Click on the Fill tab to specify the highlighter that you want to use to style the cells and click on the OK button at the bottom of the Format cells dialog box. See screenshot.

Step 9: Click on the OK button at the bottom of the New Formatting rule dialog box.

Now, you can see that the cells with different values have been highlighted with your selected color. See the below screenshot.

Method 5: Compare two excel sheet using a third-party tool – XL Comparator

XL Comparator is a free, web-based, online, simple, as well as easy-to-use tool. It is used to compare the two Excel sheets without any programming or installing software. It compares two excel sheets based on the columns.

Steps to compare two Excel sheet

There are the following steps to compare two Excel sheet using XL Comparator –

Step 1: Open any web browser and type or copy-paste the below link on it or you can simply click on the below-given link – https://www.xlcomparator.net/

Step 2: An XL Compare home page will appear on the screen. Click on the Choose File button associated with the First file to upload the first excel sheet.

Step 3: Browse the first file location and click on the Open button to upload the first excel sheet.

Step 4: Once the first excel sheet is uploaded successfully, click on the Choose File button associated with the Second file to upload the second sheet.

Step 5: Browse the second excel sheet location. Click on the Open button to upload the second excel sheet.

Step 6: After uploading both excel sheet, click on the Next button as shown in the screenshot.

Step 7: A small pop-up menu appears on the screen with the message Please wait until files are sent to XL Comparator. Bigger files mean longer time. Click on the OK button.

Step 8: On the left side, click on the File Columns drop down menu and select the column that you want to compare.

Similarly, on the right side, click on the File Columns drop down menu and select the column that you want to compare.

Click on the Every row of “First.xlsx” present in “Second.xlsx” radio button (already checked)

Click on the Next button at the bottom of the screen, as shown in the below screenshot.

Note: In our case, we want to compare the Price column.

Step 9: Click on the Click here link to download the sheet.