4 ways to compare two Excel files

4 ways to compare two Excel files
4 ways to compare two Excel files
Anonim

If there's one thing Microsoft Excel can do well, it's comparing data both within the same worksheet and between two workbooks. If you have to compare data, you will have the choice between using the SEARCH, MATCH and EXACT functions.

Steps

Method 1 of 4: Using the side-by-side function

Compare Two Excel Files Step 1

Step 1. Open the workbooks to compare

Run Excel, click File and select To open. There you will navigate the hard drive tree to find the two workbooks to compare.

Before launching Excel, put your two workbooks in the same folder. The comparison can only be done if both workbooks are displayed on the screen

Compare Two Excel Files Step 2

Step 2. Click the View tab

Activate one of the open workbooks, then click on the tab at the top Display, the seventh in the row of tabs.

Compare Two Excel Files Step 3

Step 3. Click View Side by Side

Locate the group Window and in it, an icon made of 2 vertical files placed… side by side. By clicking on it, you will have 2 data sheets, one on the right, the other on the left.

  • Of course, this feature will not be visible if there is only one open workbook: Excel cannot do the impossible!
  • If two workbooks are open, Excel considers them active and will therefore automatically put them side by side.
Compare Two Excel Files Step 4

Step 4. Click Reorder All

This feature allows you to change the side-by-side presentation of documents, as there are several ways to present them.

  • In the window that opens, choose the presentation that suits you. You have the choice between Mosaic, Horizontal, Vertical and Cascade.
Compare Two Excel Files Step 5

Step 5. Enable synchronous scrolling

Once the workbooks are arranged the way you want, click in Window on Synchronous scrolling (it's just under View side by side). As the name suggests, if you have two long workbooks, you will be able to see them in parallel no matter which part you are comparing. Very handy for a line-to-line comparison.

Compare Two Excel Files Step 6

Step 6. Scroll through either workbook

Since they are synchronous, no matter which one you handle, the other will move the same. This is really an interesting feature, even if it is not what will reveal the differences to you… if there is any!

Method 2 of 4: Using the SEARCH function

Compare Two Excel Files Step 7

Step 1. Open the workbooks to compare

Run Excel, click File and select To open. There you will navigate the hard drive tree to find the two workbooks to compare.

Before launching Excel, put your two workbooks in the same folder. The comparison can only be done if both workbooks are displayed on the screen

Compare Two Excel Files Step 8

Step 2. Create a drop-down list

It will be created in a cell that you have previously chosen, but it will not appear until later.

Compare Two Excel Files Step 9

Step 3. Click on the cell

The outline then becomes thick.

Compare Two Excel Files Step 10

Step 4. Click the DATA tab on the toolbar

In the group Data tools, click on To validate. A window appears.

  • On older versions of Excel, the data toolbar only appears after clicking on the tab. DATA. A window Data validation appears on the screen.
Compare Two Excel Files Step 11

Step 5. Display List in the Allow drop-down list

Compare Two Excel Files Step 12

Step 6. Click the icon for the Source field

This is where you will indicate the sort of pivot criterion (here, the first column) which will allow the relative information to appear.

Compare Two Excel Files Step 13

Step 7. Select the first column of your list

Confirm with the Enter key. Click on OK. Look at the cell now with a list: a small arrow has appeared on the right of the cell and if you click on it, you will see the list selected by you.

Compare Two Excel Files Step 14

Step 8. Select the cell that will display the result of the comparison

Compare Two Excel Files Step 15

Step 9. Click the Insert tab, then click Reference

With older versions of Excel, you just have to click on the tab Formulas, then on Research and referral.

Compare Two Excel Files Step 16

Step 10. Select Search and Reference

This function is in the group Function library.

Compare Two Excel Files Step 17

Step 11. Double-click SEARCH

In doing so, you bring up a dialog box. Click on OK.

Compare Two Excel Files Step 18

Step 12. Select the desired value

It will be chosen in the drop-down list of the field lookup_value.

Compare Two Excel Files Step 19

Step 13. Select the first column as the search range

This is done in the list of the field search_vector.

Compare Two Excel Files Step 20

Step 14. Select the second column for the result_vector field

It is in this column that the relative information will be displayed.

Compare Two Excel Files Step 21

Step 15. Change the value of the cell containing the drop-down list

The information relating to this value in the first column automatically appears.

Method 3 of 4: With XL Comparator

Compare Two Excel Files Step 22

Step 1. Run your usual browser

Go to this page, it's the XL Comparator website. There, you will be able to upload the two Excel files to compare to the server of this site.

Compare Two Excel Files Step 23

Step 2. Click Choose File

You have two frames (1 and 2) inside which you have a button Choose file. Click on it to download the two Excel files to compare.

Compare Two Excel Files Step 24

Step 3. Click Next>

An information message simply warns you that the processing time depends on the size of the selected files, however processing has started. Click on To close.

Compare Two Excel Files Step 25

Step 4. Select the columns to compare

Just below the filenames is a drop-down list Select a column. Click on the list and choose the 2 columns which will be the object of the comparison.

The names of the columns are classified in the direction of creation of the file (column A, then B, etc.)

Compare Two Excel Files Step 26

Step 5. Select the comparison criterion

Four types of comparison are offered. Choose one by clicking on the left radio button. Before making your choice, read what is on offer.

Compare Two Excel Files Step 27

Step 6. Select one or two particular options

It is possible, at the bottom of the page, to ignore or not the case (Disregard uppercase / lowercase) and spaces (Ignore spaces before and after values). You can check both options.

Compare Two Excel Files Step 28

Step 7. Click Next>

You arrive on the last page: the number of lines concerned is indicated and you are invited to download the result of the comparison.

Compare Two Excel Files Step 29

Step 8. Display the result of the comparison

Clicking on Click here in the framework of Download the result file, you are taken to a new page with the result of the comparison in HTML format. To save it to your hard drive, do File, then Save.

  • At the end, you have two options: change the comparison settings (Another comparison) or change the files for another comparison (Compare other files).

Method 4 of 4: Without viewing Excel files

Compare Two Excel Files Step 30

Step 1. Locate the names of the workbook and the worksheets it contains

  • We will take two binders located on the hard drive (C:) in a folder Comparative, their access paths are:

    • C: \ Comparison \ Workbook1.xls (containing a sheet of the name Sales_1999);
    • C: \ Comparison \ Workbook2.xls (containing a sheet of the name Sales_2000).
  • For each of these workbooks, the first column (A) will display the product nomenclature and the second column the number of products sold. These 2 references (product name and sales) are listed on the first line.
Compare Two Excel Files Step 31

Step 2. Create a comparison workbook

We are going to create a third workbook (Classeur3.xls) which will allow the comparison of the first 2. The first column will always contain the names of the products, but the next one will indicate the absolute variation in sales between the two dates (here, 2000 sales - 1999 sales).

  • The path of this workbook will be: C: \ Comparison \ Workbook3.xls (containing a sheet of the name Comparison).
Compare Two Excel Files Step 32

Step 3. Display the title of the first column

Only the Classeur3.xls workbook is open, click once in cell A1, then type:

  • = 'C: \ Comparative [Workbook1.xls] Sales_1999'! A1
  • If the workbook is elsewhere on your hard drive, you will replace the path C: \ Comparison \. If your workbook has another name, replace Workbook1.xls with the name in question. And finally if your worksheet is not called Sales_1999, you will replace that name. The workbook referred to in the formula (here, Workbook1.xls) must not be open on the screen. Excel can change the reference you add if the file is open. You would then end up with a cell that has the same content as the cell you are referring to.

Step 4. Copy down the contents of the first column

Grab the handle at the bottom right of the cell and drag it by as many cells as there are in column A of the unopened files. All references for that column appear.

Compare Two Excel Files Step 33

Step 5. Indicate the reference of the second column (B1)

Here, it will be Variation, since it is the variation in sales that interests us.

Compare Two Excel Files Step 34

Step 6. Show the variation in sales

Start with the first product by entering the following formula in B2:

  • = 'C: \ Comparative [Workbook2.xls] Sales_2000'! B2-'C: \ Comparison [Workbook1.xls] Sales_1999 '! B2
  • Here, we have made a subtraction, but depending on the analysis you plan to do, any operation (product, division…) can be programmed.
Compare Two Excel Files Step 35

Step 7. Drag down the calculation cell

Click and hold the mouse button in the lower right corner, then drag the cell down to reveal the variations of all products.

Popular by topic