How to compare two xlsx files

{"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/1\/1b\/Compare-Two-Excel-Files-Step-1-Version-2.jpg\/v4-460px-Compare-Two-Excel-Files-Step-1-Version-2.jpg","bigUrl":"\/images\/thumb\/1\/1b\/Compare-Two-Excel-Files-Step-1-Version-2.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-1-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

1

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

• Navigate to the folder where you have the Excel workbooks saved, select each workbook separately, and keep both workbooks open.

Advertisement

Method 2

Method 2 of 4:

Using the Lookup Function

1. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/9\/9e\/Compare-Two-Excel-Files-Step-7.jpg\/v4-460px-Compare-Two-Excel-Files-Step-7.jpg","bigUrl":"\/images\/thumb\/9\/9e\/Compare-Two-Excel-Files-Step-7.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-7.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   1

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

   • Navigate to the folder where you have the Excel workbooks saved, select each workbook separately and keep both workbooks open.

2. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/a\/a0\/Compare-Two-Excel-Files-Step-8.jpg\/v4-460px-Compare-Two-Excel-Files-Step-8.jpg","bigUrl":"\/images\/thumb\/a\/a0\/Compare-Two-Excel-Files-Step-8.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-8.jpg","smallWidth":460,"smallHeight":344,"bigWidth":728,"bigHeight":545,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   2

   Decide on which cell you would like the user to select from. This is where a drop-down list will appear later.

3. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/4\/4e\/Compare-Two-Excel-Files-Step-9.jpg\/v4-460px-Compare-Two-Excel-Files-Step-9.jpg","bigUrl":"\/images\/thumb\/4\/4e\/Compare-Two-Excel-Files-Step-9.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-9.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   3

   Click on the cell. The border should darken.

4. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/5\/53\/Compare-Two-Excel-Files-Step-10.jpg\/v4-460px-Compare-Two-Excel-Files-Step-10.jpg","bigUrl":"\/images\/thumb\/5\/53\/Compare-Two-Excel-Files-Step-10.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-10.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   4

   Click the DATA tab on the toolbar. Once you've clicked on it, select VALIDATION in the drop-down menu. A pop up should appear.

   • If you're using an older version of Excel, the DATA toolbar will pop up once you select the DATA tab and display Data Validation as the option instead of Validation.

5. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/c\/c1\/Compare-Two-Excel-Files-Step-11.jpg\/v4-460px-Compare-Two-Excel-Files-Step-11.jpg","bigUrl":"\/images\/thumb\/c\/c1\/Compare-Two-Excel-Files-Step-11.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-11.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   5

   Click List in the ALLOW list.

6. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/5\/5c\/Compare-Two-Excel-Files-Step-12.jpg\/v4-460px-Compare-Two-Excel-Files-Step-12.jpg","bigUrl":"\/images\/thumb\/5\/5c\/Compare-Two-Excel-Files-Step-12.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-12.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   6

   Click the button with the red arrow. This will let you pick your source (in other words, your first column), which will then be processed into data in the drop-down menu.

7. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/1\/1f\/Compare-Two-Excel-Files-Step-13.jpg\/v4-460px-Compare-Two-Excel-Files-Step-13.jpg","bigUrl":"\/images\/thumb\/1\/1f\/Compare-Two-Excel-Files-Step-13.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-13.jpg","smallWidth":460,"smallHeight":344,"bigWidth":728,"bigHeight":545,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   7

   Select the first column of your list and press Enter. Click OK when the data validation window appears. You should see a box with an arrow on it, which will drop-down when you click the arrow.

8. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/8\/87\/Compare-Two-Excel-Files-Step-14.jpg\/v4-460px-Compare-Two-Excel-Files-Step-14.jpg","bigUrl":"\/images\/thumb\/8\/87\/Compare-Two-Excel-Files-Step-14.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-14.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   8

   Select the cell where you want the other info to show up.

9. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/d\/dd\/Compare-Two-Excel-Files-Step-15.jpg\/v4-460px-Compare-Two-Excel-Files-Step-15.jpg","bigUrl":"\/images\/thumb\/d\/dd\/Compare-Two-Excel-Files-Step-15.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-15.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   9

   Click the Insert and Reference tabs. In older versions of Excel, you can skip clicking the Insert tab and just click on the Functions tab to pull up the Lookup & Reference category.

10. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/4\/49\/Compare-Two-Excel-Files-Step-16.jpg\/v4-460px-Compare-Two-Excel-Files-Step-16.jpg","bigUrl":"\/images\/thumb\/4\/49\/Compare-Two-Excel-Files-Step-16.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-16.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    10

    Select Lookup & Reference from the category list.

11. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/f\/f3\/Compare-Two-Excel-Files-Step-17.jpg\/v4-460px-Compare-Two-Excel-Files-Step-17.jpg","bigUrl":"\/images\/thumb\/f\/f3\/Compare-Two-Excel-Files-Step-17.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-17.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    11

    Find Lookup in the list. When you double-click it, another box should appear and you can click OK.

12. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/6\/65\/Compare-Two-Excel-Files-Step-18.jpg\/v4-460px-Compare-Two-Excel-Files-Step-18.jpg","bigUrl":"\/images\/thumb\/6\/65\/Compare-Two-Excel-Files-Step-18.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-18.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    12

    Select the cell with the drop-down list for the lookup_value.

13. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/1\/17\/Compare-Two-Excel-Files-Step-19.jpg\/v4-460px-Compare-Two-Excel-Files-Step-19.jpg","bigUrl":"\/images\/thumb\/1\/17\/Compare-Two-Excel-Files-Step-19.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-19.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    13

    Select the first column of your list for the Lookup_vector.

14. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/c\/c4\/Compare-Two-Excel-Files-Step-20.jpg\/v4-460px-Compare-Two-Excel-Files-Step-20.jpg","bigUrl":"\/images\/thumb\/c\/c4\/Compare-Two-Excel-Files-Step-20.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-20.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    14

    Select the second column of your list for the Result_vector.

15. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/f\/f1\/Compare-Two-Excel-Files-Step-21.jpg\/v4-460px-Compare-Two-Excel-Files-Step-21.jpg","bigUrl":"\/images\/thumb\/f\/f1\/Compare-Two-Excel-Files-Step-21.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-21.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

    15

    Pick something from the drop-down list. The info should automatically change.

Advertisement

Method 3

Method 3 of 4:

Using XL Comparator

1. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/c\/ce\/Compare-Two-Excel-Files-Step-22.jpg\/v4-460px-Compare-Two-Excel-Files-Step-22.jpg","bigUrl":"\/images\/thumb\/c\/ce\/Compare-Two-Excel-Files-Step-22.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-22.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   1

   Open your browser and go to //www.xlcomparator.net. This will take you to XL Comparator's website, where you can upload two Excel workbooks for comparison.

2. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/e\/e4\/Compare-Two-Excel-Files-Step-23.jpg\/v4-460px-Compare-Two-Excel-Files-Step-23.jpg","bigUrl":"\/images\/thumb\/e\/e4\/Compare-Two-Excel-Files-Step-23.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-23.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   2

   Click Choose File. This will open a window where you can navigate to one of the two Excel documents you want to compare. Make sure to select a file for both fields.

3. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/1\/18\/Compare-Two-Excel-Files-Step-24.jpg\/v4-460px-Compare-Two-Excel-Files-Step-24.jpg","bigUrl":"\/images\/thumb\/1\/18\/Compare-Two-Excel-Files-Step-24.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-24.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   3

   Click Next > to continue. Once you've selected this, a pop-up message should appear at the top of the page letting you know that the file upload process has begun and that larger files will take longer to process. Click Ok to close this message.

4. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/0\/0a\/Compare-Two-Excel-Files-Step-25.jpg\/v4-460px-Compare-Two-Excel-Files-Step-25.jpg","bigUrl":"\/images\/thumb\/0\/0a\/Compare-Two-Excel-Files-Step-25.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-25.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   4

   Select the columns you want to be scanned. Underneath each file name is a drop-down menu that says Select a column. Click on the drop-down menu for each file to select the column you want to be highlighted for comparison.

   • Column names will be visible when you click the drop-down menu.

5. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/a\/a7\/Compare-Two-Excel-Files-Step-26.jpg\/v4-460px-Compare-Two-Excel-Files-Step-26.jpg","bigUrl":"\/images\/thumb\/a\/a7\/Compare-Two-Excel-Files-Step-26.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-26.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   5

   Select contents for your result file. There are four options with bubbles next to them in this category, one of which you'll need to select as the formatting guidelines for your result document.

6. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/3\/3d\/Compare-Two-Excel-Files-Step-27.jpg\/v4-460px-Compare-Two-Excel-Files-Step-27.jpg","bigUrl":"\/images\/thumb\/3\/3d\/Compare-Two-Excel-Files-Step-27.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-27.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   6

   Select the options to ease column comparison. In the bottom cell of the comparison menu, you'll see two more conditions for your document comparison: Ignore uppercase/lowercase and Ignore "spaces" before and after values. Click the checkbox for both before proceeding.

7. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/2\/2d\/Compare-Two-Excel-Files-Step-28.jpg\/v4-460px-Compare-Two-Excel-Files-Step-28.jpg","bigUrl":"\/images\/thumb\/2\/2d\/Compare-Two-Excel-Files-Step-28.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-28.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   7

   Click Next > to continue. This will take you to the download page for your result document.

8. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/2\/28\/Compare-Two-Excel-Files-Step-29.jpg\/v4-460px-Compare-Two-Excel-Files-Step-29.jpg","bigUrl":"\/images\/thumb\/2\/28\/Compare-Two-Excel-Files-Step-29.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-29.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   8

   Download your comparison document. Once you've uploaded your workbooks and set your parameters, you'll have a document showing comparisons between data in the two files available for download. Click the underlined Click here text in the Download the comparison file box.

   • If you want to run any other comparisons, click New comparison in the bottom-right corner of the page to restart the file upload process.

Advertisement

Method 4

Method 4 of 4:

Accessing an Excel File Directly from a Cell

1. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/5\/54\/Compare-Two-Excel-Files-Step-30.jpg\/v4-460px-Compare-Two-Excel-Files-Step-30.jpg","bigUrl":"\/images\/thumb\/5\/54\/Compare-Two-Excel-Files-Step-30.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-30.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   1

   Locate your workbook and sheet names.

   • In this case, we use three example workbooks located and named as follows:
     • C:\Compare\Book1.xls (containing a sheet named "Sales 1999")
     • C:\Compare\Book2.xls (containing a sheet named "Sales 2000")
   • Both workbooks have the first column "A" with the name of the product, and the second column "B" with the amount sold each year. The first row is the name of the column.

2. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/4\/4f\/Compare-Two-Excel-Files-Step-31.jpg\/v4-460px-Compare-Two-Excel-Files-Step-31.jpg","bigUrl":"\/images\/thumb\/4\/4f\/Compare-Two-Excel-Files-Step-31.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-31.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   2

   Create a comparison workbook. We will work on Book3.xls to do a comparison and create one column containing the products, and one with the difference of these products between both years.

   • C:\Compare\Book3.xls (containing a sheet named "Comparison")

3. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/6\/6a\/Compare-Two-Excel-Files-Step-32.jpg\/v4-460px-Compare-Two-Excel-Files-Step-32.jpg","bigUrl":"\/images\/thumb\/6\/6a\/Compare-Two-Excel-Files-Step-32.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-32.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   3

   Place the title of the column. Only with "Book3.xls" opened, go to cell "A1" and type:

   • ='C:\Compare\[Book1.xls]Sales 1999'!A1
   • If you are using a different location replace "C:\Compare\" with that location. If you are using a different filename remove "Book1.xls" and add your filename instead. If you are using a different sheet name replace "Sales 1999" with the name of your sheet. Beware not to have the file you are referring ("Book1.xls") opened: Excel may change the reference you are adding if you have it open. You’ll end up with a cell that has the same content as the cell you referred to.

4. 4

   Drag down cell "A1" to list all products. Grab it from the bottom right square and drag it, copying all names.

5. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/e\/e3\/Compare-Two-Excel-Files-Step-33.jpg\/v4-460px-Compare-Two-Excel-Files-Step-33.jpg","bigUrl":"\/images\/thumb\/e\/e3\/Compare-Two-Excel-Files-Step-33.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-33.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   5

   Name the second column. In this case, we call it "Difference" in "B1".

6. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/3\/3d\/Compare-Two-Excel-Files-Step-34.jpg\/v4-460px-Compare-Two-Excel-Files-Step-34.jpg","bigUrl":"\/images\/thumb\/3\/3d\/Compare-Two-Excel-Files-Step-34.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-34.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   6

   (For example) Estimate the difference of each product. In this case, by typing in cell "B2":

   • ='C:\Compare\[Book2.xls]Sales 2000'!B2-'C:\Compare\[Book1.xls]Sales 1999'!B2
   • You can do any normal Excel operation with the referred cell from the referred file.

7. {"smallUrl":"\/\/www.wikihow.com\/images\/thumb\/6\/69\/Compare-Two-Excel-Files-Step-35.jpg\/v4-460px-Compare-Two-Excel-Files-Step-35.jpg","bigUrl":"\/images\/thumb\/6\/69\/Compare-Two-Excel-Files-Step-35.jpg\/aid7169808-v4-728px-Compare-Two-Excel-Files-Step-35.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}

   7

   Drag down the lower corner square and get all differences, as before.

Advertisement

Community Q&A

Search

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Advertisement

Tips

• Remember it is important to have the referred files closed. If you have them opened, Excel may override what you type in the cell, making it impossible to access the file afterward (unless again you have it open).

  Thanks!

  Helpful 0 Not Helpful 0

Submit a Tip

All tip submissions are carefully reviewed before being published

Submit

Thanks for submitting a tip for review!

Advertisement

You Might Also Like

Advertisement

About This Article

Advertisement