Comparing two spreadsheets can be a common necessity for various reasons. Whether you’re managing financial data, tracking project progress, or auditing information, identifying differences between spreadsheets is crucial. Microsoft Spreadsheet Compare is a powerful tool designed exactly for this purpose, allowing you to efficiently pinpoint discrepancies and understand changes between two Excel workbooks.
Important Note: Spreadsheet Compare is exclusively available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to utilize this tool.
Accessing Spreadsheet Compare
To begin using Spreadsheet Compare, the first step is to open the application.
- Navigate to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t immediately see it, simply start typing “Spreadsheet Compare.”
- Click on the Spreadsheet Compare option to launch the application.
Alongside Spreadsheet Compare, Microsoft offers a similar tool for Access databases named Microsoft Database Compare. Like Spreadsheet Compare, Database Compare is included in Office Professional Plus editions and Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare your Excel files:
-
Initiate Comparison: Click on the Home tab, and then select Compare Files. This action will open the Compare Files dialog box.
-
Select Workbooks:
- Next to the Compare box, click the blue folder icon. This will allow you to browse your file system and locate the earlier version of your Excel workbook. You can select files stored locally on your computer, on a network drive, or even input a web address if your workbooks are saved online.
- Similarly, click the green folder icon beside the To box. Browse to and select the more recent version of the workbook you want to compare against the earlier one. Click OK after selecting the files.
Tip: Spreadsheet Compare allows you to compare two files that have the same name, as long as they are located in different folders. This is particularly useful when comparing versions of the same report saved in different project folders.
-
Choose Comparison Parameters: In the left-hand pane of the Compare Files dialog box, you’ll see a list of options. These options let you specify what aspects of the workbooks you want to compare. You can check or uncheck boxes to include or exclude elements like Formulas, Macros, Cell Format, and more. To compare all aspects, simply click Select All.
-
Run the Comparison: After selecting your workbooks and comparison options, click OK to start the comparison process.
If a message appears stating “Unable to open workbook,” it’s likely that one or both of your workbooks are password protected. Click OK in the message box and then enter the password(s) for the protected workbook(s) when prompted.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid.
- Side-by-Side View: The left pane displays the workbook you selected as the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version).
- Worksheet Navigation: If your workbooks contain multiple worksheets, these are compared sheet by sheet. You can navigate between worksheets using the forward and back buttons on the horizontal scroll bar located above the grids.
- Hidden Worksheets: Importantly, Spreadsheet Compare will even compare worksheets that are hidden within the Excel files and include them in the results.
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences between the workbooks. The specific colors indicate the nature of the change, such as:
- Green Fill: Typically indicates cells where “entered values” (values not derived from formulas) have been changed.
- Green Font: Also used for “entered values” changes, often in the details pane.
- Blue-Green Fill: Indicates that a “calculated value” (a value resulting from a formula) has changed. This often happens when input values in other cells are modified, affecting formula results.
A legend explaining the color codes is usually located in the lower-left pane of the Spreadsheet Compare window, helping you quickly interpret the highlighted differences.
Example Interpretation: In a scenario comparing sales reports, if cells containing quarterly sales figures (entered values) are highlighted in green, it means those figures have been directly modified between the two versions. Consequently, if cells displaying year-to-date totals (calculated values) are highlighted in blue-green, it signifies that these totals have changed because of the updated quarterly sales figures.
Resize for Readability: If cell contents are truncated due to narrow columns in the comparison grid, click Resize Cells to Fit. This option automatically adjusts column widths to ensure you can view the complete content of each cell.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers a valuable add-in called “Inquire.” Available in Excel 2013 and later, the Inquire add-in provides a dedicated “Inquire” tab in the Excel ribbon once activated.
The Inquire tab offers a suite of analytical tools, allowing you to:
- Analyze Workbooks: Gain insights into workbook structure, formulas, and potential errors.
- Visualize Relationships: Explore connections between cells, worksheets, and even links to external workbooks, providing a comprehensive overview of data dependencies.
- Clean Excess Formatting: Remove unnecessary formatting that can bloat file size and hinder performance, streamlining your spreadsheets.
While Spreadsheet Compare is excellent for side-by-side version comparisons, the Inquire add-in is beneficial for in-depth workbook analysis and understanding complex spreadsheet structures. If you need to compare two open workbooks within Excel, you can conveniently launch Spreadsheet Compare directly from the Inquire tab using the “Compare Files” command.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Excel help or online.
Conclusion
Spreadsheet Compare is an indispensable tool for anyone who needs to effectively compare and analyze differences between Excel spreadsheets. By following these steps, you can quickly identify changes, understand modifications, and maintain data integrity across different versions of your critical workbooks. Whether you’re auditing financial data, tracking changes in project plans, or simply ensuring accuracy, mastering Spreadsheet Compare will significantly enhance your spreadsheet management efficiency.
