Strategies for Comparing Excel Documents

Access the workbooks you wish to compare. Locate these by launching Excel, selecting File, then Open, and picking the two workbooks you want to compare from the ensuing menu.

• Navigate to the directory where your Excel workbooks are stored, individually select each workbook, and ensure both remain open.

Navigate to the View tab. Once you've accessed one of the workbooks, simply click on the View tab located at the top-center of the window.

Click on View Side by Side. This option can be found in the Window group within the View menu and is represented by two sheets as its icon. This action will bring up both worksheets into smaller windows stacked vertically.

• If you have only one workbook open in Excel, this option may not be immediately visible under the View tab.
• If two workbooks are open, Excel will automatically select them for side-by-side viewing.

Click on Arrange All. This feature allows you to adjust the layout of the workbooks when they're displayed side by side.

• In the menu that appears, you can choose to display the workbooks Horizontally, Vertically, Cascade, or Tiled.

Activate Synchronous Scrolling. Once both worksheets are open, simply click on Synchronous Scrolling (located under the View Side by Side option) to facilitate scrolling through both Excel files simultaneously, enabling manual comparison of data line by line.

Scroll through one workbook to automatically scroll through the other. Once Synchronous Scrolling is enabled, navigating through both workbooks simultaneously becomes effortless, streamlining the process of comparing their data.

Access the workbooks you wish to compare. Locate these by launching Excel, clicking File, then Open, and selecting two workbooks to compare from the menu that appears.

• Navigate to the directory where you have the Excel workbooks saved, select each workbook separately, and ensure both remain open.

Choose the cell for user selection. This is where a dropdown menu will be displayed later.

Click on the designated cell. The border will darken to indicate selection.

Access the DATA tab from the toolbar. Once selected, choose VALIDATION from the dropdown menu. A dialog box will appear.

• In older Excel versions, selecting the DATA tab will automatically display the Data Validation option instead of Validation.

Choose List from the ALLOW options.

Click the button with the red arrow. This action allows you to designate your source (i.e., your first column), which will then be utilized as data in the dropdown menu.

Choose the first column of your list and press Enter. Upon clicking OK in the data validation window, you will notice a box with an arrow icon, which will expand upon clicking the arrow.

Select the cell where you wish to display additional information.

Access the Insert and Reference tabs. In previous Excel versions, you can directly access the Lookup & Reference category by skipping the Insert tab and selecting the Functions tab.

Choose Lookup & Reference from the category list.

Locate Lookup in the list. Double-click it, and another dialog box will appear; click OK.

Choose the cell with the dropdown list for the lookup value.

Choose the first column of your list as the Lookup vector.

Choose the second column of your list as the Result vector.

Select an option from the dropdown menu. The information will automatically update.

Launch your web browser and navigate to https://www.xlcomparator.net/. This will direct you to XL Comparator's website, where you can upload two Excel workbooks for comparison.

Click Choose File. This action will open a window allowing you to browse and select one of the two Excel documents you wish to compare. Ensure to choose a file for both fields.

Click Next > to proceed. After selecting this option, a notification will appear at the top of the page informing you that the file upload process has started, with larger files taking longer to process. Click Ok to dismiss this notification.

Choose the columns you wish to scan. Beneath each file name lies a dropdown labeled Select a column. Click on each dropdown to designate the column you desire to highlight for comparison.

• Column names will appear upon dropdown selection.

Determine the contents for your resultant file. Within this category, you'll find four options accompanied by bubbles. Select one of these as the formatting guidelines for your resultant document.

Select options to facilitate column comparison. At the bottom of the comparison menu, you'll find two additional conditions for your document comparison: Ignore uppercase/lowercase and Ignore 'spaces' before and after values. Tick the checkboxes for both before proceeding.

Click Next > to proceed. This action directs you to the download page for your resultant document.

Retrieve your comparison document. Upon uploading your workbooks and configuring your parameters, you'll obtain a document displaying comparisons between data in the two files, available for download. Click on the underlined Click here text within the Download the comparison file box.

• To initiate further comparisons, click New comparison in the bottom-right corner of the page to initiate the file upload process anew.

Find your workbook and sheet names.

• For instance, we have three sample workbooks named and located as follows:
  • C:\Compare\Book1.xls (with a sheet titled “Sales 1999”)
  • C:\Compare\Book2.xls (with a sheet titled “Sales 2000”)
• Both workbooks contain columns “A” for product names and column “B” for yearly sales amounts. The first row represents column headers.

Create a comparison workbook. Let's use Book3.xls for comparison. We'll generate one column with product names and another with the difference in sales between the two years.

• C:\Compare\Book3.xls (featuring a sheet named “Comparison”)

Insert the column title. With “Book3.xls” open, navigate to cell “A1” and input:

• ='C:\Compare\[Book1.xls]Sales 1999'!A1
• If your files are stored elsewhere, modify “C:\Compare\” accordingly. Adjust “Book1.xls” and “Sales 1999” if your file or sheet has different names. Ensure that the referenced file (“Book1.xls”) isn't open, as Excel may alter the reference if it's open. This will result in a cell mirroring the content of the referenced cell.

Label the second column. Here, we designate it as “Difference” in cell 'B1'.

(As an example) Calculate the variance for each product. For instance, input the following formula in cell “B2”:

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

Drag the lower corner square downwards to compute all variances, as previously described.

• Remember, it's crucial to have the referenced files closed. If they're open, Excel might overwrite your inputs, rendering it impossible to access the file afterward (unless it's open again).