An 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 making it easy to spot 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. The tool can be accessed 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 using certain criteria. Here’s how it works:
How to Analyze Two Versions of a Workbook With Microsoft Spreadsheet Compare
To analyze different versions of a workbook, you have to import both workbooks in 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 a dialog box. Step 3: Within the Compare Files dialog box, select the File icon beside the Compare (older file) option. 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. Note: If you find that 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 either ticking or unticking the checkboxes at the bottom left of the tool. The function of some of the checkboxes are explained below:
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: it 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 on the workbook. This graph can be used to identify the changes that occur the most in the workbooks.
How to Export Comparison Results From Microsoft Spreadsheet Compare
Once you click on Ok for Spreadsheet Compare to analyze changes in your worksheets, 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 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 export it: Step 1: On the Ribbon of the Spreadsheet Compare tool, select Export Results, which launches File Explorer. Step 2: Select the location you would like to save the file and input a name for the file. Step 3: Select Save. The comparison analysis should now be available at your saved location.
Copy Comparison Results to Clipboard
If you would like to use the comparison results on any other platform except Microsoft Excel, then 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.
Limitations of Spreadsheet Compare
That’s it on how to use Microsoft Spreadsheet Compare to compare different versions of an Excel file. However, the tool has a major limitation in that changes noted on the compared workbooks cannot be accepted or rejected. The tool only shows excel sheets side by side and highlights the changes to make it easier for users to identify them. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
