Buzz
This article will guide you on how to directly compare data between two Excel files. After performing the comparison, you can consider using Look Up, Index, and Match functions to assist in your analysis.
Steps
Use Excel's View Side by Side Feature
Open the spreadsheets you need to compare. You can locate the files by opening Excel, clicking on File, selecting Open, and then choosing the two spreadsheets to compare from the menu that appears.
- Navigate to the folder where your Excel files are saved, select, and open each file individually.
Click on the View tab. After opening one of the spreadsheets, you can click on the View tab located at the top center of the window.
Click on View Side by Side. This option is found in the Window group on the ribbon under the View menu and is represented by two worksheet icons. Both sets of worksheets will be displayed in smaller windows aligned vertically.
- This option may not be clearly visible under the View tab if only one spreadsheet is open in Excel.
- If two spreadsheets are open, Excel will automatically select these files for side-by-side display.
Click on Arrange All. This setting allows you to change the orientation of how the spreadsheets are displayed side by side.
- In the pop-up menu, you can choose to arrange the spreadsheets horizontally (Horizontal), vertically (Vertical), cascaded (Cascade), or tiled (Tiled).
Enable Synchronous Scrolling. After opening both sets of worksheets simultaneously, click on Synchronous Scrolling (located below the View Side by Side option) to scroll through both files in sync, making it easier to manually check for data differences.
Scroll through one spreadsheet to move both files simultaneously. Once Synchronous Scrolling is enabled, you can scroll through both spreadsheets at the same time, making data comparison effortless.
Utilize the Lookup Feature
Open the spreadsheet you need to compare. Locate the files by launching Excel, clicking on File, selecting Open, and then choosing the two spreadsheets for comparison from the displayed menu.
- Navigate to the folder where your Excel files are stored, select, and open each file individually.
Decide on the cell where you want users to click for selection. This is where the dropdown list will later appear.
Click on the cell you have chosen. The cell border will become thicker.
Click on the DATA tab in the toolbar. After clicking, select VALIDATION from the dropdown menu. A dialog box will appear.
- In older versions of Excel, the DATA toolbar will pop up after selecting the DATA tab, and the Validation option will be replaced with Data Validation.
Click on List from the ALLOW menu.
Click on the red arrow button. You can then select the source (or the first column) that will be processed into the dropdown menu data.
Select the first column for the list and press Enter. Click OK when the data validation window appears. Here, a box with an arrow next to it will dropdown when you click the arrow.
Select the cell where you want additional information to appear.
Click on the Insert tab and choose Reference. In older versions of Excel, you can skip clicking the Insert tab and directly select the Functions tab to display the Lookup & Reference category.
Select Lookup & Reference from the category list.
Locate the Lookup option in the list. Double-click on it, and another dialog box will appear where you can click OK.
Select the cell containing the dropdown list in the lookup_value row.
Choose the first column of the list in the Lookup_vector row.
Select the second column of the list in the Result_vector row.
Select an item from the dropdown menu. The information will automatically update.
Use XL Comparator
Open your web browser and go to https://www.xlcomparator.net. The XL Comparator website will open, allowing you to upload two Excel spreadsheets for comparison.
Click on Choose File. A window will open, enabling you to navigate to one of the two Excel documents for comparison. Ensure you select a file for both fields.
Click on Next > to proceed. A notification will appear at the top of the page, informing you that the file upload process has started and that larger files may take more time to process. Click Ok to close this notification.
Select the column you want to scan. Below each file name is a dropdown menu labeled Select a column. Click on the dropdown menu for each file to choose the column you want to highlight and compare.
- The column names will appear when you click the dropdown menu.
Choose the content for the final file. There are four options with radio buttons in this category, and you need to select one to guide the formatting of the resulting document.
Select options to make column comparison easier. At the end of the comparison menu are two conditions for document comparison: Ignore uppercase/lowercase and Ignore "spaces" before and after values. You need to check both boxes before proceeding.
Click on Next > to continue. You will be redirected to the download page for the comparison document.
Download the comparison document. After uploading the spreadsheets and setting the parameters, the document comparing the data in the two files will be available for download. Click on the underlined Click here line in the Download the comparison file section.
- If you want to perform another comparison, you can click New comparison at the bottom right of the page to restart the file upload process.
Access Excel files directly from the cell
Identify the worksheet and spreadsheet names and locations.
- In this case, we will use three examples of names and spreadsheet locations as follows:
- C:\Compare\Book1.xls (contains a sheet named “Sales 1999”)
- C:\Compare\Book2.xls (contains a sheet named “Sales 2000”)
- Both spreadsheets have the first column as “A” with product names and the second column as “B” with sales quantities for each year. The first row contains the column names.
Create a comparison spreadsheet. We will work on Book3.xls to perform the comparison and create a column for product names, with another column showing the differences in these products over time.
- C:\Compare\Book3.xls (contains a sheet named “Comparison”)
Set the column header. With only “Book3.xls” open, click on cell “A1” and enter:
- ='C:\Compare\[Book1.xls]Sales 1999'!A1
- If you are using a different directory, replace “C:\Compare\” with the appropriate path. If the file name is not “Book1.xls”, adjust it to the name you are using. If the sheet name is different, replace “Sales 1999” with the current name. Note: Do not open the file you are referencing (“Book1.xls”) as Excel may alter the reference if the document is open. The highlighted cell will display the same content as the referenced cell.
Drag from cell “A1” down to the end of the product list. Click on the small square at the bottom-right corner of cell “A1” and drag down to copy all product names.
Name the second column. In this case, we will label column "B1" as “Difference”.
Example: To estimate the difference for each product, enter the following into cell “B2”:
- ='C:\Compare\[Book2.xls]Sales 2000'!B2-'C:\Compare\[Book1.xls]Sales 1999'!B2
- You can perform any standard Excel operations on the referenced file's cells.
As before, drag the square at the bottom corner down to calculate all differences.
Tips
- Important note: Ensure all referenced files are closed. If any file is open, Excel may overwrite the content you enter into the cell, making the file inaccessible later (unless reopened).
