Compare Two Spreadsheets: A Step-by-Step Guide to Identify Differences

In today’s data-driven world, spreadsheets are essential tools for organizing and analyzing information. Whether you’re managing budgets, tracking inventory, or analyzing sales figures, Excel spreadsheets are likely at the heart of your operations. Often, you might find yourself working with multiple versions of the same spreadsheet or needing to compare different spreadsheets to identify changes or discrepancies. Manually comparing spreadsheets can be time-consuming and prone to errors. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to streamline this process. This guide will walk you through how to effectively Compare Two Spreadsheets using this tool, enabling you to quickly pinpoint differences and ensure data accuracy.

What is Spreadsheet Compare and Why Use It?

Spreadsheet Compare is a utility designed to highlight the differences between two Excel workbooks. This is incredibly useful in various scenarios, such as:

• Version Control: Identify changes made between different versions of a workbook.
• Error Detection: Locate discrepancies, inconsistencies, or potential errors introduced during data updates or modifications.
• Auditing: Track changes for compliance or audit trails.
• Data Validation: Ensure data consistency across different spreadsheets or datasets.

Important Note: Spreadsheet Compare is not included in all versions of Microsoft Office. It is available with specific editions like Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are unsure if you have access, follow the steps below to check if you can open the application.

Step-by-Step Guide: How to Compare Two Excel Workbooks

Here’s a detailed guide on how to compare two spreadsheets using Microsoft Spreadsheet Compare:

1. Open Spreadsheet Compare

First, you need to launch the Spreadsheet Compare application.

• For Windows users: Click the Start button, then look for Spreadsheet Compare. You can scroll through your application list or simply type “Spreadsheet Compare” to search for it. Click on the application to open it.

  If you cannot find it directly, it might be part of the “Inquire” add-in for Excel (available in some versions). However, Spreadsheet Compare itself is a standalone application, not an Excel add-in. If you truly don’t have it, you may need to check your Office version or consider upgrading to a version that includes it.

2. Select the Spreadsheets for Comparison

Once Spreadsheet Compare is open, you’ll need to specify the two Excel workbooks you want to compare.

1. Navigate to the Home tab in the Spreadsheet Compare window.

2. Click on the Compare Files button. This will open the Compare Files dialog box.

3. In the Compare box, click the blue folder icon. This allows you to browse and select the first Excel workbook (typically the older version or the first spreadsheet you want to compare). You can choose files from your computer, network locations, or even enter a web address if your files are stored online.

4. Similarly, in the To box, click the green folder icon. Browse and select the second Excel workbook (usually the newer version or the spreadsheet you are comparing against).

5. Click OK after selecting both files.

   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 file.

3. Choose Comparison Options

Before running the comparison, you can customize what aspects of the spreadsheets you want to analyze for differences.

1. In the left pane of the Compare Files dialog box, you’ll see a list of options. These options let you specify what elements to compare, such as:

   • Formulas: Compares the formulas used in cells.
   • Values: Compares the values in cells, whether they are manually entered or calculated.
   • Cell Format: Checks for differences in cell formatting, like fonts, colors, and styles.
   • Macros: Compares VBA macros if your workbooks contain them.
   • Worksheet Name: Checks if worksheet names are different.
   • Column Width & Row Height: Identifies changes in column and row dimensions.
   • Hidden Rows & Columns: Detects differences in hidden rows or columns.

2. Check or uncheck the boxes next to these options to select the aspects you want to include in the comparison. If you want to compare everything, you can simply click Select All.

3. Once you’ve chosen your options, click OK to start the comparison process.

4. Analyze the Comparison Results

After clicking OK, Spreadsheet Compare will analyze the two workbooks and display the results in a user-friendly, side-by-side grid.

• Side-by-Side Grid: The comparison results are presented in two panes. The left pane shows the “Compare” file (the first one you selected), and the right pane displays the “To” file (the second one). Each pane represents the corresponding workbook, with worksheets accessible via the horizontal scroll bar.

• Color-Coded Highlights: Differences are highlighted using distinct colors within the grid. The color coding helps you quickly understand the type of change. For instance, you might see:

  • Green Fill: Often indicates “entered values” that have changed (non-formula cells).
  • Blue-Green Fill: Suggests a “calculated value” has changed, often due to changes in input values or formulas.
  • Other Colors: Spreadsheet Compare uses a legend (usually in the lower-left pane) to explain the meaning of each color code. This legend is crucial for understanding the types of differences identified.

• Details Pane: Below the side-by-side grid, a details pane provides more specific information about each difference. This pane often lists the changed cells and the nature of the change (e.g., value changed, formula changed, format changed).

• Navigation: Use the horizontal scroll bar to navigate between worksheets if your workbooks contain multiple sheets. Even hidden worksheets are included in the comparison and displayed in the results.

• Resize Cells: If cell content is truncated due to narrow columns, click Resize Cells to Fit to automatically adjust column widths for better readability.

Understanding the Results in Detail

Interpreting the color-coded results is key to effectively using Spreadsheet Compare. Pay close attention to the legend provided, as it will explain what each color signifies. By examining the highlighted cells and the details pane, you can gain a thorough understanding of all the changes between your two spreadsheets.

For example, if you see a green fill in certain cells, it indicates that the manually entered values in those cells have been modified between the two versions. If you see a blue-green fill in calculated cells, it means that the formulas or the input values affecting those formulas have changed, leading to different calculated results.

Beyond Spreadsheet Compare: Excel’s Inquire Add-in

For more advanced spreadsheet analysis within Excel itself, consider exploring the Inquire add-in. Available in some Excel versions (often the same versions that include Spreadsheet Compare), the Inquire add-in provides a dedicated “Inquire” tab in Excel.

From the Inquire tab, you can perform tasks like:

• Workbook Analysis: Get a comprehensive overview of a workbook’s structure, formulas, and potential issues.
• Relationship Exploration: Visualize relationships between cells, worksheets, and even external workbooks.
• Excess Formatting Cleanup: Remove unnecessary formatting to reduce file size and improve performance.

While Spreadsheet Compare is excellent for side-by-side comparisons, the Inquire add-in offers a broader suite of analytical tools directly within Excel. To enable the Inquire add-in, you may need to activate it through Excel’s options.

Conclusion

Comparing two spreadsheets manually is a tedious and error-prone task. Microsoft Spreadsheet Compare offers a robust and efficient solution to compare two spreadsheets and quickly identify differences. By following this step-by-step guide, you can leverage this powerful tool to enhance data accuracy, streamline version control, and improve your overall spreadsheet management practices. Whether you’re tracking changes, auditing data, or simply ensuring consistency, Spreadsheet Compare is an invaluable asset for anyone working extensively with Excel spreadsheets.