Comparing two versions of an Excel spreadsheet to identify changes can be a tedious and error-prone task. Whether you’re auditing financial data, tracking project updates, or ensuring data integrity, pinpointing the exact differences is crucial. Fortunately, Microsoft Excel offers a powerful tool called Spreadsheet Compare, designed to streamline this process and highlight discrepancies between two workbooks efficiently. This guide will walk you through how to use Spreadsheet Compare to quickly and accurately find differences in your Excel spreadsheets.
Getting Started with Spreadsheet Compare
Spreadsheet Compare is a utility available as part of Microsoft Office Professional Plus suites (2013, 2016, 2019) and Microsoft 365 Apps for enterprise. It’s specifically designed to compare Excel files and generate detailed reports on their differences.
Opening Spreadsheet Compare:
- Navigate to your Start Menu in Windows.
- Look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare” and it should appear in the search results.
- Click on Spreadsheet Compare to launch the application.
Alongside Spreadsheet Compare, Microsoft also provides Database Compare for Access databases, which is also included in the same Office Professional Plus and Microsoft 365 Apps for enterprise packages.
Step-by-Step Guide to Comparing Excel Files
Let’s delve into the process of comparing two Excel workbooks to find differences:
-
Initiate the Comparison: Once Spreadsheet Compare is open, click on Home > Compare Files. This action will open the Compare Files dialog box.
-
Select the Files to Compare: In the Compare Files dialog, you’ll see two boxes: Compare and To.
- Click the blue folder icon next to the Compare box. Browse to and select the older version of your Excel workbook. You can select files from your local computer, network drives, or even enter a web address if your files are stored online.
- Next, click the green folder icon next to the To box. Browse to and select the more recent version of the workbook you want to compare against the older one. Click OK to confirm your selection.
Tip: You can effectively compare two files that have the same name but are located in different folders.
-
Choose Comparison Options: In the left-hand pane of the Compare Files dialog, you can specify what aspects of the workbooks you want to compare. You can choose from options like Formulas, Macros, Cell Format, and more. To compare everything, simply click Select All.
-
Run the Comparison: Click OK to initiate the comparison process. Spreadsheet Compare will analyze both workbooks based on your selected options.
If a workbook is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and enter the password for the protected workbook when prompted. Spreadsheet Compare can work with password-protected files, allowing you to compare them effectively.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a user-friendly, two-pane grid.
- Side-by-Side View: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). Below the grids, a details pane provides further information about the identified differences.
- Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located above the grid. Even hidden worksheets are included in the comparison and displayed in the results.
- Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences found. Different fill colors or text font colors indicate specific changes. For example, cells containing “entered values” (values not derived from formulas) that have been modified are typically highlighted with a green fill color in the grid and a green font in the results list. A legend in the lower-left pane clearly explains the meaning of each color code.
In the example above, the green fill in cells E2:E5 indicates changed “entered values” between the two versions. Consequently, the blue-green fill in the YTD column (F2:F4 and E6:F6) signifies that “calculated values” have also changed due to the updated input values. Furthermore, cell F5 highlights a corrected formula in the newer version.
- Resize Cells for Better View: If cell contents are truncated due to column width, click Resize Cells to Fit to automatically adjust column widths for optimal readability.
Exploring Excel’s Inquire Add-in
Besides Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon, offering additional workbook analysis capabilities. From the Inquire tab, you can analyze workbook structure, visualize relationships between cells and worksheets, and even remove excessive formatting.
To use Spreadsheet Compare directly from within Excel, you can utilize the “Compare Files” command found within the Inquire add-in when you have two workbooks open.
If you don’t see the Inquire tab, you may need to activate it in Excel’s options.
Conclusion
Spreadsheet Compare is an invaluable tool for anyone who needs to compare two Excel spreadsheets for differences. It simplifies the process of identifying changes, whether they are in data values, formulas, or formatting. By using Spreadsheet Compare, you can save significant time and effort while ensuring the accuracy and integrity of your Excel data. For organizations managing critical Excel workbooks and Access databases, Microsoft offers more comprehensive solutions like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server to further enhance change management and risk mitigation.
