How to Compare Two XLSX Files: A Comprehensive Guide

Comparing two XLSX files is a common task for professionals who work with data, spreadsheets, and reports. Whether you are auditing financial records, tracking changes in project data, or ensuring data integrity across different versions of a document, knowing how to effectively compare Excel files is crucial. Microsoft Excel provides a built-in tool called Spreadsheet Compare (part of the Inquire add-in) that simplifies this process. This guide will walk you through how to use Spreadsheet Compare to identify differences between two XLSX files, understand the results, and explore additional features for advanced analysis.

Understanding Spreadsheet Compare

Spreadsheet Compare is a utility designed to highlight the differences between two Excel workbooks. It is especially useful when you need to:

• Identify changes: Pinpoint exactly what has been modified between two versions of the same workbook.
• Audit data: Ensure consistency and accuracy by comparing datasets across different files.
• Troubleshoot errors: Locate discrepancies in formulas or data entries that might be causing issues.
• Merge changes: Understand the modifications made by different collaborators before merging workbooks.

Important Note: Spreadsheet Compare is included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Excel, you may need to check if this feature is available or consider upgrading to a version that includes it.

Opening Spreadsheet Compare

To begin comparing your XLSX files, you first need to open the Spreadsheet Compare application. Here’s how:

1. Go to the Start Menu in Windows.
2. Type Spreadsheet Compare.
3. Click on Spreadsheet Compare to launch the application.

If you cannot find it directly, typing “Spreadsheet Compare” in the search bar should bring it up.

In addition to Spreadsheet Compare for Excel files, Microsoft also offers Database Compare for Access databases, which functions similarly for comparing Access files and is included in the same Office Professional Plus and Microsoft 365 Apps for enterprise suites.

Step-by-Step Guide to Comparing Two Excel Workbooks

Once Spreadsheet Compare is open, follow these steps to compare your XLSX files:

1. Initiate Comparison: Click Home > Compare Files. This action will open the Compare Files dialog box.

2. Select the “Compare” File (Older Version): Click the blue folder icon next to the Compare box. Browse to the location of the older version of your Excel workbook. You can select files from your local computer, network drives, or even enter a web address if your files are stored online.

3. Select the “To” File (Newer Version): Click the green folder icon next to the To box. Locate and select the newer version of the workbook you want to compare against the older one. Click OK to confirm your selection.

   Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders. This is particularly useful for version control.

4. Choose Comparison Options: In the left pane of the Compare Files dialog box, you can customize what aspects of the workbooks you want to compare. Select or deselect options such as Formulas, Macros, Cell Format, Comments, and more. For a comprehensive comparison, you can simply click Select All.

5. Run the Comparison: Click OK to start the comparison process.

   If either of the workbooks is password-protected, you might encounter an “Unable to open workbook” message. Click OK and enter the password for the respective workbook when prompted. Spreadsheet Compare can handle password-protected files, ensuring you can still analyze and compare them.

Interpreting Comparison Results

After running the comparison, Spreadsheet Compare displays the results in a user-friendly, two-pane grid.

• Side-by-Side View: The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer version).
• Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located below the grid. Even hidden worksheets are included in the comparison and shown in the results.
• Color-Coded Differences: Differences between the files are highlighted with distinct colors. The color coding helps you quickly identify the type of change. For instance:
  • Green fill: Indicates “entered values” (cells with manually entered data, not formulas) that have been changed.
  • Blue-green fill: Signifies “calculated values” (cells containing formulas) where the results have changed.
  • Other colors: May indicate changes in formatting, formulas, or other elements, depending on the options you selected for comparison.
• Legend Pane: The lower-left pane serves as a legend, explaining what each color code represents. Refer to this pane to fully understand the types of differences highlighted in the comparison.

Example Scenario: Imagine you are comparing two versions of a sales report. In the older version, the Q4 sales figures were preliminary. The updated version contains the final Q4 numbers. Spreadsheet Compare would highlight the cells with updated sales figures (entered values) in green. Consequently, if the year-to-date (YTD) totals are calculated using formulas that include Q4 sales, these calculated cells would also be highlighted, perhaps in blue-green, indicating a change in calculated values due to the updated input data.

Furthermore, Spreadsheet Compare can also detect formula corrections. If a formula was incorrect in the older version and has been fixed in the newer version, this change will also be flagged, helping you identify and rectify errors in your spreadsheets.

If cell content is truncated due to column width, you can click Resize Cells to Fit to expand columns and view the full content.

Leveraging Excel’s Inquire Add-in for Deeper Analysis

Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in, which offers a range of powerful tools for workbook analysis. To access these features, you may need to activate the Inquire add-in first.

Enabling the Inquire Add-in:

If you don’t see the Inquire tab in your Excel ribbon:

1. Go to File > Options > Add-ins.
2. In the Manage dropdown at the bottom, select COM Add-ins and click Go.
3. Check the box next to Inquire and click OK.

Once enabled, the Inquire tab appears in your Excel ribbon, providing access to features like:

• Workbook Analysis: Provides a comprehensive overview of a workbook’s structure, formulas, errors, and hidden information.
• Relationship Analysis: Visualizes the relationships between cells, worksheets, and even external workbooks, helping you understand data flow and dependencies.
• Excess Formatting Cleanup: Removes unnecessary formatting that can bloat file size and slow down performance.

The Compare Files command within the Inquire add-in in Excel is essentially a shortcut to launch the Spreadsheet Compare application directly from within Excel, streamlining your workflow when you are already working with the files you want to compare.

Next Steps and Advanced Solutions

For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers advanced management and auditing solutions. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis capabilities, helping organizations mitigate risks associated with user-developed tools in Excel and Access by providing better oversight and control.

Further Resources:

• Overview of Spreadsheet Compare: https://o15.officeredir.microsoft.com/r/rlidssc15
• Turn on the Inquire add-in: https://go.microsoft.com/fwlink/?LinkID=324744 (Note: the original article link was for the English version, using a general link for broader accessibility)
• What you can do with Spreadsheet Inquire: https://go.microsoft.com/fwlink/?LinkID=324742 (Note: the original article link was for the English version, using a general link for broader accessibility)

By mastering Spreadsheet Compare and exploring the Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the integrity of your Excel data. This guide provides a solid foundation for effectively comparing XLSX files and leveraging Excel’s built-in tools for data analysis and management.