Comparing worksheets in Excel is a common task for professionals who need to track changes, audit data, or ensure consistency across different versions of a spreadsheet. Whether you’re managing budgets, analyzing sales figures, or collaborating on projects, knowing how to effectively compare Excel worksheets can save you time and prevent errors. Microsoft Spreadsheet Compare, a powerful tool available with certain Office versions, offers a robust solution for this. This guide will walk you through the process of using Spreadsheet Compare to identify differences between Excel workbooks, helping you maintain data integrity and accuracy.
To get started, you’ll need to access Spreadsheet Compare. This tool is included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you have one of these versions, you can easily open Spreadsheet Compare to begin analyzing your Excel files.
Opening Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application.
-
Access via Start Menu: Click the Start button on your Windows desktop and look for Spreadsheet Compare in your applications list. If you don’t immediately see it, simply start typing “Spreadsheet Compare,” and it should appear as a search result. Select Spreadsheet Compare to open the application.
Alt Text: Accessing Spreadsheet Compare application from the Start Menu, type Spreadsheet Compare to find and open the tool.
Spreadsheet Compare is designed specifically for Excel workbook comparisons, and it has a companion program called Database Compare for Access databases. Both tools are valuable for users who need to meticulously compare files and track changes.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, you can easily initiate a comparison between two Excel workbooks. Follow these steps to compare your files:
-
Initiate Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box, where you will specify the workbooks you want to analyze.
-
Select the Original Workbook: In the Compare Files dialog box, locate the Compare box. Click the blue folder icon next to it to browse your files. You can select workbooks stored on your computer, a network drive, or even enter a web address if your files are saved online. Choose the earlier version of your workbook that you want to use as the basis for comparison.
-
Select the Revised Workbook: Next, find the To box in the dialog box and click the green folder icon beside it. Browse to and select the workbook you want to compare against the original version. This is typically the more recent version of your file. Click OK to confirm your selection.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders. This is particularly useful when comparing versions of the same report saved in different project folders.
-
Choose Comparison Options: Before running the comparison, you can customize what aspects of the workbooks you want to examine. In the left pane of the Compare Files dialog box, you’ll see a list of options such as Formulas, Macros, and Cell Format. Select the checkboxes next to the elements you want to include in your comparison. For a comprehensive analysis, you can simply click Select All to compare all aspects of the workbooks.
-
Run the Comparison: After selecting your workbooks and comparison options, click OK to start the comparison process. Spreadsheet Compare will analyze the two files based on your chosen settings.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password protected. Click OK in the message box and enter the password for the protected workbook when prompted. Spreadsheet Compare needs the password to access and compare the contents of password-protected files.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid. The left pane shows the “Compare” workbook (the older version), and the right pane displays the “To” workbook (the newer version). A detailed information pane appears below the grids, providing specific information about the detected changes.
-
Side-by-Side Worksheet View: Each worksheet from both workbooks is compared and displayed side-by-side. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located at the bottom of the grid. Even hidden worksheets are included in the comparison and results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to indicate the type of differences found between the workbooks. The cell fill color and text font color are used to distinguish different types of changes. For instance, cells containing “entered values” (values that are not the result of formulas) are typically highlighted with a green fill in the grid and a green font in the results list. A legend in the lower-left pane clearly explains what each color code represents, making it easy to interpret the comparison results.
Alt Text: Example of Spreadsheet Compare results showing color-coded differences between two Excel workbook versions, highlighting changes in entered values and calculated formulas.
For example, in the illustration above, changes in manually entered quarterly figures are marked with green, while changes in year-to-date totals due to formula updates are marked with blue-green. This color-coding helps quickly identify the nature and location of changes.
-
Cell Content Visibility: If some cells are too narrow to display their full content in the comparison grid, you can click Resize Cells to Fit. This option automatically adjusts the column widths to ensure that all cell contents are visible, improving readability and analysis.
Exploring Excel’s Inquire Add-in as an Alternative
Besides Spreadsheet Compare, Excel itself offers another powerful tool for workbook analysis: the Inquire add-in. Available in Excel 2013 and later versions, the Inquire add-in provides an “Inquire” tab in the Excel ribbon, offering various features for workbook analysis, including:
- Workbook Analysis: Provides a comprehensive overview of a workbook’s structure, formulas, and potential errors.
- Relationship Exploration: Allows you to visualize relationships between cells, worksheets, and even links to other workbooks.
- Excess Formatting Cleanup: Helps to remove unnecessary formatting that can increase file size and complexity.
While the Inquire add-in doesn’t directly compare two workbooks side-by-side like Spreadsheet Compare, it offers valuable tools for understanding and analyzing complex Excel files. If you need to compare two open workbooks in Excel using Spreadsheet Compare, you can directly launch Spreadsheet Compare from the Inquire tab using the “Compare Files” command.
If you don’t see the Inquire tab in your Excel, you may need to activate it. You can find instructions on how to Turn on the Inquire add-in on Microsoft Support. For a deeper understanding of the Inquire add-in’s capabilities, refer to the guide on What you can do with Spreadsheet Inquire.
Next Steps for Advanced Workbook Management
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server are designed to provide robust change management, inventory, and analysis features for Excel and Access files. These tools help reduce risks associated with user-developed tools and ensure better control and governance over important spreadsheet data.
Conclusion
Comparing worksheets in Excel using Microsoft Spreadsheet Compare is an efficient way to identify changes and discrepancies between different versions of your workbooks. This tool is particularly useful for auditing, version control, and ensuring data accuracy in collaborative environments. By following the steps outlined in this guide, you can effectively use Spreadsheet Compare to enhance your Excel workflow and maintain the integrity of your spreadsheet data. For further information, you can also review the Overview of Spreadsheet Compare for a comprehensive understanding of its features and capabilities.
