How to Use Microsoft Spreadsheet Compare to Analyze Excel File Versions

Microsoft Excel has a feature that allows users to track changes made to a workbook. However, once the changes are accepted on the workbook, the track of change is lost forever. So what happens when you need to revert or refer to the previous version of the workbook?

One option you can try is Microsoft Spreadsheet Comparison. Microsoft Spreadsheet Comparison allows users to easily compare two versions of an Excel file. The tool then highlights the differences between the two files, making it easy to spot changes.

System requirements for using Microsoft Spreadsheet Comparison

You can access Microsoft Spreadsheet Compare in Microsoft 365, Office Professional Plus 2013, Office Professional Plus 2016, and Office Professional Plus 2019. The tool is accessible from the Microsoft Office Tools program group.

For the comparison to work, it is necessary to have different versions of the spreadsheet. The tool then analyzes the files and highlights the differences according to certain criteria. This is how it works:

How to analyze two versions of a workbook with Microsoft Worksheet Comparison

To analyze different versions of a workbook, you need to import both workbooks into the Worksheet Comparison Tool. Here’s how to do it:

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

2nd step: 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 next to Compare (older file). Select the Excel file in the file explorer and click Open.

Step 4: Select the File icon next to the To (newer file) option in the Compare Files dialog box. Select the Excel file in the file explorer and click Open.

Step 5: Click OK to begin the comparison process.

To note: If you find that the order of comparing files is mixed up, you can click Swap to change the position of each workbook.

Step 6: You can streamline what the spreadsheet comparison tool analyzes by checking or unchecking the boxes at the bottom left of the tool. The function of some check boxes is explained below:

• Entered values: it highlights a change in data entry in workbooks.
• Calculated values: it highlights the difference in the calculated values ​​in the workbooks.
• Formulas: it highlights a change in the formulas in the workbooks.
• Names: it highlights the difference in the cell names in the workbooks.
• Macro: it highlights the difference in VBA code in workbooks.

A chart in the lower right corner of the tool ranks the different types of changes on the workbook. This chart can be used to identify the changes that occur the most in workbooks.

How to export comparison results from Microsoft Spreadsheet Comparison

After you click OK to compare worksheets to analyze the changes made to your worksheets, the identified changes are highlighted in different text and cell fill colors. The results of the comparison can either be used directly on the tool or exported to another platform. Here’s how to do it:

Export the comparison results to Microsoft Excel

By exporting the comparison results to Microsoft Excel, you can perform further analysis on it. Here’s how to export it:

Step 1: On the spreadsheet comparison tool ribbon, select Export Results, which launches File Explorer.

2nd step: Select the location where you want to save the file and enter a name for the file.

The comparison analysis should now be available at your saved location.

Copy the results of the comparison to the clipboard

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

Step 1: On the spreadsheet comparison tool ribbon, select Copy results to clipboard.

2nd step: Launch an application to paste the copied data.

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

Limitations of comparing worksheets

This is how to use Microsoft Spreadsheet Compare to compare different versions of an Excel file. However, the tool has a major limitation in that the changes noted on the compared workbooks cannot be accepted or rejected. The tool only displays Excel sheets side by side and highlights the changes to make it easier for users to identify them.