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 change traces are lost forever. So what happens when you need to revert or refer to a previous version of a workbook?
One option you can try is Microsoft Spreadsheet Compare. Microsoft Spreadsheet Compare makes it easy for users to compare two versions of an Excel file. The tool then highlights the differences between the two files to easily detect changes.
Requirements to Use Microsoft Spreadsheet Compare
You can access Microsoft Spreadsheet Compare in Microsoft 365, Office Professional Plus 2013, Office Professional Plus 2016 and Office Professional Plus 2019. This tool can be accessed from the Microsoft Office Tools program group.
For the comparison to work, different versions of the spreadsheet are required. The tool then analyzes the files and highlights the differences using certain criteria. Here’s how it works:
How to analyze two versions of a workbook with the Microsoft Compare spreadsheet
To analyze different versions of a workbook, you must import both workbooks into the Spreadsheet Compare tool. 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 the dialog box.
Step 3: In the Compare Files dialog box, select the File icon next to the Compare (older files) option. 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 the order of file comparisons confused, you can click Swap to change the position of each workbook.
Step 6: You can streamline what the Spreadsheet Compare tool analyzes by ticking or unmarking the checkboxes at the bottom left of the tool. The function of some checkboxes is explained below:
- Entered value: marks the change in the input data in the workbook.
- Calculated value: it highlights the difference in calculated values in the workbook.
- Formulas: it highlights the change in formulas in the workbook.
- Name: it highlights the difference in cell names in the workbook.
- Macro: it highlights the difference in the VBA code in the workbook.
A chart in the bottom right corner of the tool, ranking the different types of changes on the workbook. This chart can be used to identify the most frequent changes in a workbook.
How to export comparison results from a Microsoft Compare spreadsheet
After you click Ok to Compare Spreadsheet to analyze the changes in your worksheet, the identified changes will be highlighted with different cell and text 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
By exporting the comparison results to Microsoft Excel, you can conduct further analysis on it. Here’s how to output it:
Step 1: On the Ribbon of the Spreadsheet Compare tool, select Export Results, this will launch File Explorer.
Step 2: Select the location where you want to save the file and enter a name for the file.
Step 3: Select Save.
Comparative analysis is now available at your saved location.
Copy the comparison results to the Clipboard
If you want to use the comparison results on any other platform except Microsoft Excel, then copying to clipboard is the best choice. 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 application to paste the copied data.
Step 3: Select Ctrl + V on the keyboard to paste.
Limitations of Spreadsheet Compare
That’s how to use Microsoft Spreadsheet Compare to compare different versions of an Excel file. However, this tool has a major limitation that it cannot accept or reject noted changes on compared workbooks. This tool just shows the excel sheets side by side and highlights the changes to make them easier for users to identify.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
