Comparing Excel spreadsheets to identify differences can be a tedious and error-prone task. Whether you’re managing financial data, tracking project progress, or auditing records, pinpointing changes between versions is crucial. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare, designed to streamline this process. This guide will walk you through how to effectively compare Excel spreadsheets for differences using this powerful utility.
What is Microsoft Spreadsheet Compare?
Microsoft Spreadsheet Compare is a specialized tool designed to highlight the discrepancies between two Excel workbooks or different versions of the same workbook. It generates a detailed report outlining changes, additions, and deletions in formulas, values, formatting, and more. This tool is invaluable for ensuring data integrity, identifying errors, and understanding modifications made over time.
Important Note: Spreadsheet Compare is included with specific Microsoft Office suites, namely 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 access the tool.
Opening Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward.
- From the Start Menu: Click the Start button and look for “Spreadsheet Compare” in your apps list. If you don’t see it immediately, simply type “Spreadsheet Compare” to search.
- Launch the Application: Click on the Spreadsheet Compare application to open it.
Alongside Spreadsheet Compare, you might also find Microsoft Database Compare, a similar tool for Access databases, which is also part of the Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Step-by-Step Guide: Comparing Two Excel Workbooks
Let’s delve into the process of comparing two Excel files using Spreadsheet Compare.
-
Initiate the Comparison: In Spreadsheet Compare, navigate to the Home tab and click Compare Files. This action will open the Compare Files dialog box.
-
Select the Original Workbook: Locate the “Compare” box. Click the blue folder icon next to it to browse and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the Revised Workbook: Next, find the “To” box and click the green folder icon. Browse to and select the more recent version of the workbook you want to compare against the original. Click OK to confirm your selection.
Tip: You can compare two files even if they share the same name, as long as they are saved in different folders.
-
Customize Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options. Choose the elements you want to include in your comparison report by checking the corresponding boxes. Options include Formulas, Macros, and Cell Format. For a comprehensive comparison, you can simply click Select All.
-
Execute the Comparison: Once you’ve selected your files and options, click OK to initiate the comparison process.
- Handling Password-Protected Workbooks: 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 you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and analyze the file content.
Understanding the Comparison Results
After running the comparison, the results are displayed in a clear, two-pane grid.
-
Side-by-Side View: The workbook you selected as “Compare” (typically the older version) is shown on the left pane, while the “To” workbook (usually the newer version) is displayed on the right. A detailed pane below the grids provides further information about the identified differences.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, each worksheet is compared against its counterpart in the other file. You can navigate between worksheets using the forward and back buttons on the horizontal scroll bar located above the grids.
Note: Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to visually represent the types of differences found. Cell fill colors and text font colors are used to indicate changes. For instance, cells containing “entered values” (cells with manually typed data, not formulas) that have been modified are typically highlighted with a green fill color in the side-by-side grid and a green font in the results list pane. A legend in the lower-left pane clearly explains the meaning of each color code.
Example Interpretation: Consider a scenario where you are comparing quarterly sales reports. In the older version, the Q4 figures were preliminary. The updated workbook contains the final Q4 sales data. In the comparison results:
- Cells in the Q4 data column (e.g., E2:E5) in both versions might have a green fill, indicating that entered values have changed.
- Consequently, calculated values in dependent columns, like “Year-to-Date” (YTD), will also reflect these changes. Cells containing these updated calculated results (e.g., F2:F4, E6:F6) could be highlighted with a blue-green fill, signifying that calculated values have changed.
Furthermore, Spreadsheet Compare can detect formula corrections. In our example, if a formula in the older workbook was incorrect (e.g., summing only Q1-Q3 data instead of Q1-Q4), and it was corrected in the updated version, this change would also be highlighted, providing valuable insight into formula accuracy.
- Resize Cells for Better Visibility: If cell contents are truncated due to narrow column widths, click Resize Cells to Fit to automatically adjust column widths for better readability of the cell data.
Leveraging Excel’s Inquire Add-in for Deeper Analysis
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides an “Inquire” tab in the Excel ribbon upon activation. This add-in offers a suite of analytical tools that complement Spreadsheet Compare.
The Inquire add-in enables you to:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even external workbooks.
- Visualize Workbook Dependencies: Explore graphical representations of formula dependencies and data flow within your spreadsheets.
- Clean Excess Formatting: Identify and remove unnecessary cell formatting that can bloat file size and hinder performance.
While Spreadsheet Compare excels at pinpointing differences between workbooks, the Inquire add-in provides tools for a more in-depth analysis of individual workbooks and their complex structures. You can even launch Spreadsheet Compare directly from Excel using the “Compare Files” command within the Inquire tab if you have two workbooks open simultaneously.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. Refer to Microsoft’s documentation on “Turn on the Inquire add-in” for step-by-step instructions. To learn more about the capabilities of this add-in, explore “What you can do with Spreadsheet Inquire.”
Conclusion: Streamline Excel Workbook Comparison for Enhanced Accuracy
Microsoft Spreadsheet Compare is an indispensable tool for anyone who regularly works with Excel spreadsheets and needs to compare Excel spreadsheets for differences. It simplifies the often-complex task of identifying changes between workbook versions, enhancing data accuracy, and improving workflow efficiency. By leveraging this tool, you can save valuable time and minimize the risk of errors when managing and updating critical Excel data.
For organizations managing mission-critical Excel workbooks and Access databases, consider exploring Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These advanced tools offer comprehensive change management, inventory, and analysis features, further mitigating risks associated with user-developed Excel and Access solutions.
Further Resources:
