It’s a common scenario: you have two Excel files, perhaps different versions of the same report or workbook, and you need to pinpoint the discrepancies. Whether you’re auditing data, merging updates, or simply troubleshooting errors, identifying the exact differences between two Excel files can be a time-consuming manual task. Thankfully, Microsoft offers a built-in tool called Spreadsheet Compare, designed to streamline this process and highlight variations, formula discrepancies, and potential issues with ease.
Important Note: Before we dive in, it’s crucial to note that Spreadsheet Compare is not included in every version of Microsoft Office. This powerful tool is specifically available with 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 Office, you may need to consider upgrading or exploring alternative comparison methods.
Accessing Spreadsheet Compare
Opening Spreadsheet Compare is straightforward, although the method may slightly vary depending on your Windows version.
For Windows 10 and later:
- Click the Start button, typically located in the bottom-left corner of your screen.
- Scroll through the application list or simply start typing “Spreadsheet Compare”.
- You should see Spreadsheet Compare appear as a search suggestion or application option. Click on it to launch the program.
For older versions of Windows:
- Click the Start button and navigate to All Programs or All Apps.
- Look for the Microsoft Office folder.
- Within the Microsoft Office folder, you should find Spreadsheet Compare listed as a separate application.
It’s worth mentioning that alongside Spreadsheet Compare, Microsoft also provides a similar tool for Access databases called Microsoft Database Compare. Both tools are valuable assets for users working extensively with Office files and require the Office Professional Plus or Microsoft 365 Apps for enterprise licenses.
Step-by-Step Guide: Comparing Two Excel Workbooks
Once you have Spreadsheet Compare open, the process of comparing two Excel files is intuitive:
-
Initiate Comparison: Click on Home > Compare Files. This action will open the Compare Files dialog box, which is your control panel for selecting the files you want to analyze.
-
Select the “Compare” File (Older Version): In the Compare Files dialog box, locate the Compare box. To the right of this box, you’ll see a blue folder icon. Click this icon to browse your file system and locate the earlier version of your Excel workbook. You are not limited to local files; you can also input a web address if your workbooks are stored on a network location or SharePoint site.
-
Select the “To” File (Newer Version): Next, find the To box in the Compare Files dialog. Click the green folder icon adjacent to it. Browse to the location of the more recent Excel workbook that you intend to compare against the older version. Click OK after selecting the file.
Tip: Spreadsheet Compare allows you to compare two files even if they share the same name, as long as they are saved in different folders. This is particularly useful when you have version-controlled files.
-
Choose Comparison Options: The left pane of the Compare Files dialog box presents a list of options that dictate what aspects of the workbooks will be compared. You can customize your comparison by checking or unchecking options such as Formulas, Macros, Cell Format, Worksheet Name, and more. For a comprehensive comparison, you can simply click Select All.
-
Run the Comparison: Once you’ve selected your files and comparison options, click OK to initiate the comparison process. Spreadsheet Compare will analyze both workbooks based on your chosen settings.
Password Protected Files: If either of your workbooks is password-protected, you may encounter an “Unable to open workbook” message. If this occurs, click OK on the message and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can handle password-protected files, ensuring you can still compare even sensitive data.
Deciphering the Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid. The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). Below these grids, a details pane provides a summary and legend for the comparison.
Changes and differences are visually highlighted using color-coding. The color scheme is designed to quickly draw your attention to specific types of modifications:
- Green Fill: Often indicates “entered values” or cells where the value has been directly inputted (not derived from a formula) and has changed between the two versions.
- Green Font: Also related to entered values, but may be used in the results list pane to highlight changes.
- Blue-Green Fill: Typically signifies changes in “calculated values,” meaning cells containing formulas where the result of the formula has changed due to modifications in input cells or formula adjustments.
- Other Colors: Spreadsheet Compare uses a range of colors to represent different types of changes, including formatting changes, structural changes, and formula modifications. The legend in the lower-left pane of the results window provides a complete guide to the color-coding system.
Worksheet Navigation: If your Excel files contain multiple worksheets, Spreadsheet Compare will compare them sheet by sheet. You can navigate between worksheets using the forward and back buttons located on the horizontal scroll bar within the comparison grid. Even hidden worksheets are included in the comparison and displayed in the results.
Cell Content Visibility: If some cells are too narrow to fully display their content in the comparison grid, you can easily resolve this by clicking the Resize Cells to Fit option. This will automatically adjust column widths to ensure all cell contents are readable.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer an additional feature called the Inquire add-in. This add-in provides a dedicated “Inquire” tab in Excel, offering a suite of analytical tools. While Spreadsheet Compare is designed for side-by-side file comparison, the Inquire add-in focuses on in-depth workbook analysis within Excel itself.
With the Inquire add-in, you can:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even links to external workbooks.
- Visualize Workbook Dependencies: Create interactive diagrams illustrating how data flows within your workbook.
- Clean Excess Formatting: Identify and remove unnecessary formatting that can bloat file size and impact performance.
If you have two Excel workbooks open simultaneously and wish to compare them, you can conveniently launch Spreadsheet Compare directly from Excel using the “Compare Files” command within the Inquire tab.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. You can find instructions on how to Turn on the Inquire add-in. To explore the full capabilities of this add-in, refer to the guide on What you can do with Spreadsheet Inquire.
Taking Data Management Further
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, Microsoft provides advanced management and auditing solutions. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server offer robust change management, inventory, and analysis features. These server-based tools are designed to mitigate risks associated with user-developed tools in Excel and Access, ensuring data integrity and control within larger organizations.
To learn more about the overarching capabilities of Spreadsheet Compare, you can explore the Overview of Spreadsheet Compare. By mastering Spreadsheet Compare and related tools, you can significantly enhance your efficiency and accuracy when working with and managing Excel data.
