Comparing two Excel spreadsheets is a common task for professionals who need to track changes, audit data, or consolidate information. Whether you’re dealing with different versions of a budget, sales reports, or project plans, identifying discrepancies quickly and accurately is crucial. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose. This guide will walk you through how to effectively use Spreadsheet Compare to analyze differences between two Excel workbooks, ensuring data integrity and saving you valuable time.
Understanding Microsoft Spreadsheet Compare
Spreadsheet Compare is a utility included with specific versions of Microsoft Office, particularly Office Professional Plus 2013, 2016, 2019, and Microsoft 365 Apps for enterprise. It’s designed to highlight the differences between two Excel files, pinpointing changes in formulas, values, formatting, and more. This tool is invaluable for anyone who needs to:
- Audit spreadsheets: Verify changes made to critical data.
- Troubleshoot errors: Identify discrepancies in calculations or data entry.
- Merge data: Understand how different versions of a spreadsheet have evolved.
- Ensure consistency: Confirm that formulas and formats are applied uniformly across workbooks.
If you have a compatible version of Office, Spreadsheet Compare offers a robust solution for detailed spreadsheet analysis.
Step-by-Step Guide to Comparing Excel Spreadsheets
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare. Follow these steps to effectively identify differences:
1. Open Spreadsheet Compare
First, you need to launch the Spreadsheet Compare application.
-
For Windows: Click the Start button, then look for Spreadsheet Compare. If you don’t see it immediately, type “Spreadsheet Compare” and select it from the search results.
Alt Text: Screenshot showing how to open Spreadsheet Compare from the Windows Start Menu by typing “Spreadsheet Compare” in the search bar.
2. Select Files for Comparison
Once Spreadsheet Compare is open, you’ll need to specify the two Excel workbooks you want to compare.
-
Click Home > Compare Files. This will open the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. This allows you to browse and select the older or first version of your Excel workbook. Navigate to the file location and select it. You can compare files from your local computer, network drives, or even enter a web address if your files are stored online.
-
Next, in the To box, click the green folder icon. Browse to and select the second Excel workbook you want to compare against the first one. This is typically the newer or modified version. Click OK to confirm your selections.
Alt Text: Spreadsheet Compare’s “Compare Files” dialog box, highlighting the blue and green folder icons used to select the ‘Compare’ and ‘To’ Excel files for comparison.
Tip: You can compare two files even if they have the same name, as long as they are saved in different folders. This is useful when comparing versions of the same report saved in different locations.
3. Choose Comparison Options
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare will analyze.
-
In the left pane of the Compare Files dialog box, you’ll see a list of options. Select the checkboxes next to the elements you want to include in the comparison, such as:
- Formulas: Compares the formulas used in cells.
- Macros (VBA): Checks for differences in VBA code.
- Cell Format: Examines formatting differences like fonts, colors, and number formats.
- Values: Compares the numerical and text values in cells.
- Worksheet Structure: Analyzes changes in worksheet order, hidden sheets, and sheet names.
-
You can also choose Select All to compare all aspects of the workbooks.
4. Run the Comparison
Once you’ve selected your files and comparison options, click OK in the Compare Files dialog box to initiate the comparison.
- If either of the workbooks is password-protected, you might see an “Unable to open workbook” message. Click OK and enter the password when prompted. Spreadsheet Compare needs to access the files to perform the analysis.
Interpreting the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid.
-
Side-by-Side View: The left pane shows the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer one). Each pane displays the worksheets from the respective workbooks. You can navigate between worksheets using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to indicate different types of changes:
- Green Fill: Typically indicates “entered values” (non-formula cells) that have been changed.
- Green Font: Also used for entered value changes, especially in the results list below the grids.
- Blue-Green Fill: Usually signifies changes in “calculated values” (formula cells). This means the result of a formula has changed, often due to changes in input values.
- Other colors may be used to represent different types of formatting or structural changes. Refer to the legend in the lower-left pane for a complete explanation of the color codes.
Alt Text: Screenshot of Spreadsheet Compare results showing two Excel worksheets side-by-side, with color-coded highlights indicating differences in cell values and formulas.
-
Detailed Results Pane: Below the two grids, a pane provides a detailed list of the changes found. This list often categorizes changes by type and location, making it easier to navigate and understand the specific modifications.
-
Resize Cells: If cell contents are truncated due to column width, click Resize Cells to Fit to expand column widths and view the complete data.
By carefully examining the color-coded grid and the detailed results pane, you can gain a thorough understanding of the differences between your two Excel spreadsheets.
Beyond Spreadsheet Compare: Excel’s Inquire Add-in
For more advanced Excel analysis, consider exploring the Inquire add-in, available in Excel 2013 and later. While Spreadsheet Compare is a standalone application, Inquire integrates directly into Excel, providing additional analytical capabilities.
- Inquire Tab: Once enabled, the Inquire add-in adds an “Inquire” tab to the Excel ribbon. From this tab, you can access tools to:
- Analyze Workbook: Provides an overview of workbook structure, formulas, errors, and links.
- Workbook Relationship: Visually maps dependencies between cells, worksheets, and even other workbooks.
- Clean Excess Cell Formatting: Optimizes workbook performance by removing unnecessary formatting.
- Compare Files (within Excel): Allows you to launch Spreadsheet Compare directly from within Excel if you have two workbooks open.
If you don’t see the Inquire tab, you may need to activate it in Excel’s options. The Inquire add-in, combined with Spreadsheet Compare, offers a comprehensive suite of tools for managing and analyzing Excel spreadsheets effectively.
Conclusion
Comparing two Excel spreadsheets is simplified and made highly efficient with Microsoft Spreadsheet Compare. By following the steps outlined in this guide, you can quickly identify differences, understand changes, and maintain the integrity of your data. Whether you are auditing financial reports, tracking project progress, or simply managing different versions of your data, Spreadsheet Compare is an indispensable tool in your Excel toolkit. For users needing even more advanced analysis features, Excel’s Inquire add-in provides further depth and control over your spreadsheet data.
