Microsoft Excel has a feature that allows users to track changes made to a workbook. However, once changes are accepted on the workbook, the changing trail is lost forever. So, what happens when you need to revert or refer to the previous version of the workbook?
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.
Was this helpful?
Last updated on 17 April, 2023
Read Next
Top 8 Ways to Fix Microsoft Excel Spreadsheet Not Saving on Mac
Although macOS comes with the default Numbers app to create and edit spreadsheets, most users prefer Microsoft Excel for familiarity and rich functions.
3 Ways to Insert a PDF Document into an Excel Spreadsheet on Windows
There's always a running joke on the internet - no matter what you study, or where you work, end of the day Microsoft Excel will end up being the most
3 Ways to Insert an Excel Spreadsheet into a Word Document
One of the benefits of using the Microsoft Office suite of products is the integration among the apps.
3 Ways to Compare Columns in Excel for Matches
Excel users can input data into a worksheet using different methods.
How to Open and Analyze Dump Files on Windows
If you are a Windows user, you have likely encountered a Blue Screen of Death (BSOD) error.
How to Enable Single Click in Windows 11 and Earlier Versions
Double-clicking the left mouse button is the conventional way to open apps and windows on Windows PCs.
4 Versions of Google Chrome: Key Differences
Google Chrome is arguably the most popular browser used to surf the internet in the present time but most of us are unaware of the fact that there are four
How to Compare Two Files in Notepad++: A Comprehensive Guide
Notepad++ is a versatile and feature-rich text editor catering to the needs of programmers, writers, and anyone who deals with text files.
The article above may contain affiliate links which help support Guiding Tech. The content remains unbiased and authentic and will never affect our editorial integrity.
