Comparing spreadsheets is a common task for professionals who work with data. Whether you are auditing financial records, tracking changes in project data, or ensuring data consistency across different versions of a workbook, the ability to efficiently Compare Spreadsheets is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to help you identify differences between Excel workbooks or different versions of the same workbook. This guide will walk you through how to use Spreadsheet Compare to effectively analyze and compare your spreadsheets, ensuring accuracy and saving valuable time.
What is Spreadsheet Compare?
Microsoft Spreadsheet Compare is a utility specifically designed to highlight the differences between two Excel workbook files. It is particularly useful for identifying discrepancies, errors, or intentional changes made between versions. This tool is not just about spotting visual differences; it delves into the details, comparing formulas, VBA code, cell formats, and more. It’s an invaluable asset for anyone needing to maintain data integrity and track changes in Excel files.
Important Note: Spreadsheet Compare is included in 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 this feature.
Step-by-Step Guide to Comparing Spreadsheets
Let’s explore how to use Spreadsheet Compare to analyze your Excel files effectively.
Opening Spreadsheet Compare
The first step is to launch the application. Unlike Excel itself, Spreadsheet Compare is a separate utility. Here’s how to open it:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare. You might find it in the Microsoft Office or Office Tools folder.
- If you don’t see it immediately, simply type “Spreadsheet Compare” in the search bar, and the application should appear.
- Click on Spreadsheet Compare to open the tool.
In addition to Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, which functions similarly for comparing Access files.
Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, you can initiate the comparison process:
-
On the Home tab, click Compare Files. This action opens the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. This allows you to browse and select the older version of your Excel workbook. You can choose files from your local computer, network locations, or even enter a web address if your files are stored online.
-
Next to the To box, click the green folder icon. Browse to and select the more recent version of the workbook that you want to compare against the older one. Click OK after selecting the file.
Tip: You can easily compare files with identical names if they are saved in different folders. This is very useful when comparing versions saved in different project folders or date-stamped folders.
-
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare should analyze. In the left pane of the dialog box, you’ll see a list of options such as Formulas, Macros, Cell Format, and more. Select the checkboxes next to the elements you want to include in the comparison. For a comprehensive analysis, you can simply check Select All.
-
Click OK to start the comparison.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password-protected. Click OK on the message, and you will be prompted to enter the password(s) for the protected file(s). Spreadsheet Compare needs to access the content to perform the comparison.
Understanding Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid.
- The left pane displays the workbook you selected as the “Compare” file (typically the older version).
- The right pane shows the “To” file (usually the newer version).
- Below the two grids, a details pane provides a summary and legend of the differences.
Differences are highlighted using color-coding, making it easy to quickly identify what has changed. The color codes represent different types of changes, such as:
- Green fill: Indicates “entered values” (cells with manually inputted data, not formulas) that have been modified.
- Blue-green fill: Signifies “calculated values” (cells containing formulas) where the results have changed.
- Different colors may represent other types of changes, such as formatting differences or structural changes.
The lower-left pane includes a legend that clearly explains what each color represents, ensuring you can accurately interpret the comparison results.
In the side-by-side grid, each worksheet from one file is compared against the corresponding worksheet in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. It’s important to note that even hidden worksheets are included in the comparison and will be displayed in the results.
If the content within cells is too wide to be fully displayed, you can click Resize Cells to Fit to automatically adjust column widths for better readability.
Exploring Excel’s Inquire Add-in
For users of Excel 2013 and later, Microsoft provides another powerful tool called the Inquire add-in. While Spreadsheet Compare is a standalone application, Inquire is integrated directly into Excel as an add-in. Once enabled, it adds an “Inquire” tab to your Excel ribbon.
The Inquire add-in offers a range of analytical capabilities beyond just comparing files. It allows you to:
- Analyze a workbook: Understand the structure, formulas, and potential errors within a single workbook.
- Show workbook relationships: Visualize the connections between different worksheets, cells, and even external workbooks.
- Clean excess cell formatting: Reduce file size and improve performance by removing unnecessary formatting.
If you have Excel workbooks open and need to quickly initiate a comparison using Spreadsheet Compare, you can do so directly from Excel using the Compare Files command within the Inquire add-in.
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. To learn more about the full capabilities of this add-in, refer to What you can do with Spreadsheet Inquire.
Next Steps for Advanced Spreadsheet Management
For organizations that heavily rely on Excel workbooks and Access databases for critical operations, consider implementing Microsoft’s advanced management tools. Microsoft Audit and Control Management Server provides robust change management and auditing features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis tools to help manage and mitigate risks associated with user-developed spreadsheets and databases.
These server solutions provide a higher level of control and oversight, particularly important for businesses needing to ensure compliance, data accuracy, and process integrity when using spreadsheets extensively.
For a broader overview of Spreadsheet Compare and its features, you can also visit Overview of Spreadsheet Compare.
By leveraging Spreadsheet Compare and the Inquire add-in, you can significantly enhance your ability to manage, compare, and audit your spreadsheets, leading to improved data accuracy and more efficient workflows. Start comparing your spreadsheets today to unlock these benefits.
