How to Compare Two Excel Files

This article will show you how to directly compare data between two Excel files. After manipulating and comparing information, you might consider using Look Up, Index, and Match to aid in analysis.

Use Excel's View Side by Side feature

How to Compare Two Excel Files Picture 1

Open the spreadsheet you need to compare. You can find the file by opening Excel, clicking File , clicking Open , and then selecting the two worksheets to compare from the menu that appears.

Go to the folder where you saved your Excel spreadsheet, select and open each file individually.

How to Compare Two Excel Files Picture 2

Click the View tab. After opening either spreadsheet, you can click the View tab in the top center of the window.

How to Compare Two Excel Files Picture 3

Click View Side by Side . This option is in the Window group on the ribbon under the View menu and has a two-sheet icon. Both sets of sheets will be placed in a smaller window and placed vertically.

This option may not be clearly visible under the View tab if you only have one worksheet open in Excel.

If there are two spreadsheets open, Excel will automatically select these files as documents to display in parallel mode.

How to Compare Two Excel Files Picture 4

Click Arrange All . This setting allows you to change orientation when spreadsheets are displayed side by side.

In the pop-up menu, you can choose to have your spreadsheet horizontal ( Horizontal ), vertical ( Vertical ), cascading ( Cascade ), or tiled ( Tiled ).

How to Compare Two Excel Files Picture 5

Enable Synchronous Scrolling. After opening two sets of sheets at the same time, click Synchronous Scrolling (located under the View Side by Side option ) to be able to scroll lineally across both files and manually check for data differences. work easier.

How to Compare Two Excel Files Picture 6

Scroll on a spreadsheet so that the screen scrolls through both files. Once the Synchronous Scrolling feature is enabled, you will be able to scroll on both spreadsheets at the same time and compare data easily.

Use the Lookup feature

How to Compare Two Excel Files Picture 7

Go to the folder where you saved your Excel spreadsheet, select and open each file individually.

How to Compare Two Excel Files Picture 8

Decide which cell you want users to click to select. This is where the drop-down list will be displayed later.

How to Compare Two Excel Files Picture 9

Click the cell of your choice. The cell border will become darker.

How to Compare Two Excel Files Picture 10

Click the DATA tab in the toolbar. After you click it, select VALIDATION in the drop-down menu. A dialog box will appear.

On older versions of Excel, the DATA toolbar will pop up after you select the DATA tab , and the Validation option will be replaced with Data Validation .

How to Compare Two Excel Files Picture 11

Click List from the ALLOW list.

How to Compare Two Excel Files Picture 12

Click the button with the red arrow. You can proceed to select the source (or first column) that will then be processed into data in the drop-down menu.

How to Compare Two Excel Files Picture 13

Select the first column for the list and press Enter . Click OK when the data value window appears. Here there is a frame with an arrow next to it, which will drop down when you click the arrow.

How to Compare Two Excel Files Picture 14

Select the cell where you want other information to display.

How to Compare Two Excel Files Picture 15

Click the Insert tab and select Reference. On old versions of Excel, you can skip clicking the Insert tab and just select the Functions tab to display the Lookup & Reference category .

How to Compare Two Excel Files Picture 16

Select Lookup & Reference from the category list.

How to Compare Two Excel Files Picture 17

Find the Lookup item in the list. Double click on it, another dialog box will appear and you can select OK .

How to Compare Two Excel Files Picture 18

Select the cell containing the drop-down list in the lookup_value line.

How to Compare Two Excel Files Picture 19

Select the first column of the list in the Lookup_vector line.

How to Compare Two Excel Files Picture 20

Select the second column of the list in the Result_vector line.

How to Compare Two Excel Files Picture 21

Select something from the drop-down menu. Information will automatically change.

Use XL Comparator

How to Compare Two Excel Files Picture 22

Open a web browser and visit https://www.xlcomparator.net . XL Comparator's website will open, where you will be able to upload two Excel spreadsheets for comparison.

How to Compare Two Excel Files Picture 23

Click Choose File . A window will open allowing you to go to one of the two Excel documents you want to compare. Check to make sure you select a file for both fields.

How to Compare Two Excel Files Picture 24

Click Next > to continue. A notification will then pop up at the top of the page letting you know that the file upload has started and that large files may take a long time to process. Click Ok to close this message.

How to Compare Two Excel Files Picture 25

Select the column you want to scan. Below each file name is a Select a column drop-down menu . Click each file's drop-down menu to select the column you want to highlight and compare.

The column name will display when you click the drop-down menu.

How to Compare Two Excel Files Picture 26

Select the content for the last file. There are four options with radio buttons next to them in this category, one of which you need to select as a formatting guide for the resulting document.

How to Compare Two Excel Files Picture 27

Select options to easily compare columns. At the bottom of the comparison menu are two conditions to compare documents: Ignore uppercase/lowercase and Ignore "spaces" before and after values (Ignore spaces before and after values). treatment). You need to check both boxes before continuing.

How to Compare Two Excel Files Picture 28

Click Next > to continue. You will be redirected to the comparison document download page.

How to Compare Two Excel Files Picture 29

Download comparison document. After you upload the spreadsheet and set the parameters, the document comparing the data in the two files will be available for download. Click the underlined Click here line in the Download the comparison file box.

If you want to make another comparison, you can click New comparison in the lower right corner of the page to start the file upload process again.

Access Excel files directly from the cell

How to Compare Two Excel Files Picture 30

Locate the sheet name and workbook.

In this case, we will use three examples of worksheet names and locations as follows:

C:CompareBook1.xls (contains worksheet named 'Sales 1999')

C:CompareBook2.xls (contains worksheet named 'Sales 2000')

Both spreadsheets have the first column 'A' with the product name, the second column 'B' with the quantity sold each year. The first row is the name of the column.

How to Compare Two Excel Files Picture 31

Create a comparison spreadsheet. We will operate on Book3.xls to compare and create one column containing the product names, the other column represents the differences of these products over time.

C:CompareBook3.xls (contains sheet named 'Comparison')

How to Compare Two Excel Files Picture 32

Set a column title. With just 'Book3.xls' open, click cell 'A1' and enter:

='C:Compare[Book1.xls]Sales 1999'!A1

If you are using another directory, replace 'C:Compare' with that path. If the file name is not 'Book1.xls', please correct it with the name you are using. In case the sheet name is also different, you need to replace 'Sales 1999' with the current name. Note: do not open the file you are referencing ('Book1.xls') because Excel may change the reference you are adding if the document is open. As a result, the currently highlighted cell will have the same content as the cell you referenced.

Drag from cell 'A1' all the way down the product list. Click on the square in the lower right corner of cell 'A1' and drag down to copy the entire product name.

How to Compare Two Excel Files Picture 33