This article discusses how to directly compare information between two Excel files. Once you are able to manipulate and compare the information, you can use the " Look Up ", " Index " and " Match " functions to help with the analysis.
Step
Method 1 of 4: Using the “Side by Side” Display Feature in Excel
Step 1. Open the workbook that needs to be compared
You can find the book file by opening Excel, clicking the " File ", choose " Open ”, and clicking on the two workbooks you want to compare in the menu that appears.
Visit the workbooks storage folder, select each book separately, and keep both book windows open
Step 2. Click the View tab
After opening a book, you can click the tab “ View ” at the top center of the Excel window.
Step 3. Click View Side by Side
This option is in the “Window” section of the menu “ View ” and is indicated by a two-sheet icon. The two worksheets will be displayed in a smaller, vertically stacked Excel window.
- This option may not be available on the “ View ” if you have only one workbook open in Excel.
- If there are two workbooks open, Excel will automatically select both documents to view side by side.
Step 4. Click Arrange All
This setting allows you to change the orientation of the workbook when viewed side-by-side.
In the menu that appears, you can select an option to view both workbooks (e.g. “ Horizontal ”, “ vertical ”, “ Cascade ", or " Tiled ”).
Step 5. Enable the “Synchronous Scrolling” feature
After opening both workbooks, click the “ Synchronous Scrolling ” (under option “ View Side by Side ”) so that you can easily scroll through both Excel worksheets per row and manually check for differences in data.
Step 6. Drag one of the workbooks to scroll through both pages
Once the “Synchronous Scrolling” feature is enabled, you can scroll through both workbook pages at the same time and easily compare existing data.
Method 2 of 4: Using the “Lookup” Function
Step 1. Open the two workbooks that need to be compared
You can find it by opening Excel, clicking on the menu “ File ", click " Open ”, and selects two workbooks to compare from the menu.
Visit the workbook storage folder, select each book separately, and keep both book windows open
Step 2. Specify the boxes that can be selected by the file user
In this box, a drop-down list will be displayed.
Step 3. Click the box
The grid frame will appear darker after clicking.
Step 4. Click the DATA tab on the toolbar
Once the tab is clicked, select VALIDATION ” in the drop-down menu. A pop-up menu will appear after that.
If you are using an older version of Excel, the “DATA” toolbar will appear after you select the “tab. DATA ”, and displays the option “ Data Validation "in lieu of option" Validation ”.
Step 5. Click List on the “ALLOW” list
Step 6. Click the button with the red arrow
This button allows you to select the source (in other words, the first column) to be processed into data for the drop-down menu.
Step 7. Select the first column on the list and press Enter key
Click OK ” when the data validation window is displayed. You can see a box with an arrow in it. This arrow will display a drop-down list when clicked.
Step 8. Select the box that you want to use to display other information
Step 9. Click the Insert and Reference tab
In earlier versions of Excel, you don't need to click the “ Insert " and can directly select the tab " Functions ” to display category “ Lookup & Reference ”.
Step 10. Select Lookup & Reference from the list of categories
Step 11. Look for Lookup on the list
Once the option is double-clicked, another box will be displayed and you can select the option “ OK ”.
Step 12. Select the box with the drop-down list for the “lookup_value” entry
Step 13. Select the first column in the list for the “Lookup_vector” entry
Step 14. Select the second column in the list for the “Result_vector” entry
Step 15. Select an entry from the drop-down list
The information will change automatically after that.
Method 3 of 4: Using XL Comparator Service
Step 1. Open a browser and visit
You will be taken to the XL Comparator website where you can upload the two Excel workbooks you want to compare.
Step 2. Click Choose File
A new window will open and you can search for one of the two Excel documents you want to compare. Make sure you select the file through the two available fields on the web page.
Step 3. Click Next > to continue
Once the option is selected, a pop-up message will be displayed at the top of the page informing you that the file upload process is already underway, and large files may take longer to process. Click Ok ” to close the message.
Step 4. Select the column you want to scan
Under each file name, there is a drop-down menu labeled “ Select a column Click the drop-down menu on each file to select the columns you want to mark and compare.
The column names will be displayed when you click the drop-down menu
Step 5. Select the content for the result file
There are four options with balloons next to them in this category. One of them you need to select as the format guide for the result document.
Step 6. Select an option for easy column comparison
In the bottom box in the comparison menu, you will see two additional filter conditions for document comparison: “ Ignore uppercase/lowercase " and " Ignore "spaces" before and after values " Click the checkboxes on both options before continuing.
Step 7. Click Next > to continue
You will be taken to the download page of the resulting document afterwards.
Step 8. Download the comparison document
Step 1. Find the workbook and worksheet name
-
In this method, we will use three sample workbooks that are saved and named as follows:
- C:\Appeals\Books1.xls (loads the worksheet labeled “Sales 1999”)
- C:\Appeals\Books2.xls (loads a spreadsheet labeled “2000 Sales”)
- Both workbooks have the first column “A” with the product name, and the second column “B” with the number of sales each year. The first row is the column name.
Step 2. Create a comparison workbook
You need to create Buku3.xls to compare the data. Use one column to display the product name, and the next column for the difference in product sales between the years being compared.
C:\Appeals\Books3.xls (loads a worksheet labeled “Difference”)
Step 3. Place the title in the column
Just open the “Book3.xls” worksheet, then click the “A1” box and type:
- ='C:\Appeal\[Book1.xls]Sales 1999'!A1
- If you saved the file in a different directory, replace “C:\Banding\” with the address of that directory. If you are using a different file name, replace “Book1.xls” and enter an appropriate file name. If you use a different sheet name/label, replace “Sales 1999” with the appropriate sheet name/label. Remember not to open the referenced file (“Book1.xls”). Excel can change the added reference if you open it. You can actually get a box with the same content/data as the box that is used as a reference.
Step 4. Drag the “A1” box downwards to display all product lists
Click the lower right corner of the box and drag downwards to display all the names.
Step 5. Name the second column
For this example, you can type “Difference” in the “B1” box.
Step 6. Estimate the difference between each product sale (as an example for this article)
In this example, type the following entry into the “B2” box:
- ='C:\Appeal\[Book2.xls]Sales 2000'!B2-'C:\Appeal\[Book1.xls]Sales 1999'!B2
- You can use normal Excel operations with the source data box from the reference file.
