How to Use Spreadsheet Compare to Analyze Excel File Differences

Key Takeaways: 

• You can access Microsoft Spreadsheet Compare in Microsoft 365, Office Professional Plus 2013, Office Professional Plus 2016, and Office Professional Plus 2019.
• All workbooks for comparison should be imported into the Spreadsheet Compare tool before comparison.

How to Analyze Two Versions of a Workbook With Microsoft Spreadsheet Compare

You must import both workbooks into the Spreadsheet Compare tool to analyze different workbook versions. Here’s how to do so:

Step 1: From the Start menu, search for Spreadsheet Compare and launch it.

Step 2: On the Home Tab, in the Compare group, select Compare Files to launch a dialog box.

Step 3: In the Compare Files dialog box, select the File icon beside the Compare (older file) option. Then, select the Excel file in File Explorer and click on Open.

Step 4: Select the File icon beside the To (newer file) option within the Compare Files dialog box. Select the Excel file in File Explorer and click on Open.

Step 5: Click on OK to begin the comparison process.

You should be able to view the Excel sheets side by side.

Note: If the order of the file comparison is mixed up, you can click on Swap to change the position of each workbook.

Step 6: You can streamline what the Spreadsheet Compare tool analyzes by ticking or unticking the checkboxes at the bottom left. Here is what some of these checkboxes do:

• Entered Values: It highlights a change in the data input within the workbooks.
• Calculated Values: It highlights the difference in the computed values within the workbooks.
• Formulas: This highlights a change in the formulas within the workbooks.
• Names: It highlights the difference in cell names within the workbooks.
• Macros: It highlights the difference in the VBA code within the workbooks.

A graph at the bottom right corner of the tool ranks the different types of changes in the workbook. This graph can help identify the changes that occur the most in the workbooks.

Export Comparison Results From Microsoft Spreadsheet Compare

Once you click OK for Spreadsheet Compare to analyze changes in your worksheets, identified changes are highlighted in different text and cell fill colors. The comparison results can be used directly on the tool or exported to another platform. Here’s how to do so:

Export Comparison Results to Microsoft Excel

You can conduct further analysis by exporting the comparison results to Microsoft Excel. Here’s how to export it:

Step 1: On the Ribbon of the Spreadsheet Compare tool, select Export Results, which launches File Explorer.

Step 2: Select the location to save the file and input a name.

Step 3: Select Save.

The comparison analysis should be available at your saved location.

Copy Comparison Results to Clipboard

If you want to use the comparison results on any other platform except Microsoft Excel, copying to the clipboard is the best option. Here’s how to do so:

Step 1: On the Ribbon of the Spreadsheet Compare tool, select Copy Results to Clipboard.

Step 2: Launch an app to paste the copied data.

Step 3: Select Ctrl + V on your keyboard to paste.