Microsoft Excel has a feature that allows users toTrack changes made to a workbookHowever, once you accept changes to a workbook, the changed path is lost forever. So, what happens when you need to undo or revert to a previous version of a workbook? In this article, we'll learn how to use Microsoft Spreadsheet Compare to analyze Excel file versions.
One option you can try is Microsoft Spreadsheet Compare, which makes it easy for users to compare two versions of an Excel file. The tool then highlights the differences between the two files, making it easier to spot changes.
Microsoft Spreadsheet Compare User Requirements
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 suite of programs.
For the comparison to work, you need to have different versions of the spreadsheet. The tool then analyzes the files and highlights the differences using specific criteria. Here's how it works:
How to Analyze Two Versions of a Workbook Using Microsoft Spreadsheet Compare
To analyze different versions of a workbook, you must import both workbooks into the Spreadsheet Compare tool. Here's how:
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 start a dialog box.
Step 3: In the Compare Files dialog box, select the file icon next to the Compare option (older file). Select the Excel file in 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 File Explorer and click Open.
Step 5: Click OK to start the comparison process.
Note: If you find that the file comparison order is mixed up, you can click “Switch” to change the position of each workbook.
Step 6: You can simplify what the Spreadsheet Compare tool analyzes by selecting or deselecting the checkboxes in the lower left of the tool. The function of some checkboxes is explained below:
- Input Values: Highlights the change in data input within workbooks.
- Calculated Values: Highlights the difference in calculated values within workbooks.
- Formulas: Highlights changes to formulas within workbooks.
- Names: Highlights the difference in cell names within workbooks.
- Macros: The difference is highlighted in the VBA code within workbooks.
The chart in the lower-right corner of the tool organizes the different types of changes in a workbook. This chart can be used to identify the most frequently occurring changes in workbooks.
How to export comparison results from Microsoft Spreadsheet Compare
After clicking OK to compare worksheets to analyze the changes made to your worksheets, selected changes are highlighted with different text and cell fill colors. The comparison results can be used directly in the tool or exported to another platform. Here's how:
Export comparison results to Microsoft Excel
By exporting your comparison results to Microsoft Excel, you can perform further analysis. Here's how to export it:
Step 1: On the Spreadsheet Compare toolbar, select Export Results, which launches File Explorer.
Step 2: Select the location where you want to save the file and enter a name for the file.
The comparison analysis should now be available in your saved location.
Copy comparison results to clipboard
If you want to use the comparison results on any platform other than Microsoft Excel, the best option is to copy them to the clipboard. Here's how:
Step 1: On the Spreadsheet Compare toolbar, select Copy Results to Clipboard.
Step 2: Launch an application to paste the copied data.
Step 3: Press Ctrl+V on your keyboard to paste it.
Spreadsheet comparison limitations
Thus, we have learned how to use Microsoft Spreadsheet Compare to analyze and compare versions of Excel files. However, the tool has a major limitation: it does not accept or reject changes observed in the compared workbooks. The tool only displays Excel sheets side-by-side and highlights changes for easy identification by users.
