How to Compare in Excel: A Comprehensive Guide to Spreadsheet Compare

It’s a common scenario: multiple people collaborate on an Excel workbook, or you’re working with different versions of the same file. You might find yourself asking, “What exactly changed in this spreadsheet?” Fortunately, Microsoft Excel offers a built-in tool called Spreadsheet Compare, designed to pinpoint these changes and display them clearly. This guide will walk you through how to effectively Compare In Excel using Spreadsheet Compare, ensuring you can easily track modifications and maintain data integrity.

Important: Spreadsheet Compare is a powerful feature, but it’s only included in specific Microsoft Office suites: Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Office, you might not have access to this tool.

Step-by-Step Guide to Compare Excel Files

Let’s dive into the practical steps to use Spreadsheet Compare and effectively compare Excel files:

Open Spreadsheet Compare

The first step is to launch the Spreadsheet Compare tool. It’s a standalone application, not directly within Excel itself.

1. Navigate to your Start menu in Windows.
2. Look for “Spreadsheet Compare.” You might find it under “Microsoft Office Tools” or by simply searching for “Spreadsheet Compare”.
3. Click on “Spreadsheet Compare” to open the application.

Choose Comparison Options

Once Spreadsheet Compare is open, you’ll see a pane on the lower-left side. This section allows you to customize what aspects of your workbooks you want to compare in Excel.

1. In the lower-left pane, you’ll see checkboxes for various elements.
2. Select the options relevant to your comparison. These can include:
   • Formulas: Compares the formulas used in cells.
   • Cell Formatting: Identifies differences in cell styles, fonts, colors, and other formatting.
   • Macros: Compares VBA macros within the workbooks.
   • Comments: Checks for changes in cell comments.
   • Blank Cells: Highlights differences in blank cells.
3. If you want to compare everything, simply click “Select All“.

Select Files to Compare

Now, you need to specify the two Excel files you want to compare in Excel. Spreadsheet Compare refers to these as the “Compare” file (the earlier version) and the “To” file (the later version you are comparing against).

1. On the Home tab of the Spreadsheet Compare ribbon, click “Compare Files“.

2. The “Compare Files” dialog box will appear.

3. In the “Compare” row, click the Browse button.

4. Navigate to and select the earlier version of your Excel workbook. You can choose files from your computer, network locations, or even enter a web address if your workbooks are saved online.

5. In the “To” row, click the Browse button.

6. Select the later version of the workbook that you want to compare against the earlier version.

   Note: You can compare two files even if they have the same name, as long as they are saved in different folders.

7. Click “OK” to initiate the compare in Excel process.

Run the Comparison and Understand Results

After you click “OK,” Spreadsheet Compare will analyze the two workbooks based on your selected options. The results are then presented in a clear, visual format.

1. The comparison results are displayed in a two-pane grid.
2. The left pane shows the “Compare” file (earlier version).
3. The right pane displays the “To” file (later version).
4. A details pane below the grids provides further information about the changes.
5. Changes are highlighted with different colors, making it easy to quickly identify modifications. The lower-left pane includes a legend explaining the color-coding.

Note: If you encounter an “Unable to open workbook” message, it’s likely that one or both workbooks are password-protected. Click “OK” and you will be prompted to enter the password(s). Spreadsheet Compare can work with password-protected files, but you’ll need to provide the passwords to access them.

Deep Dive into Comparison Results

Understanding how Spreadsheet Compare presents the results is key to effectively compare in Excel.

Side-by-side Grid View

The core of the comparison is the side-by-side grid.

• For each workbook, worksheets are compared sequentially, starting from the leftmost sheet in each file.
• Even if a worksheet is hidden in one of the workbooks, Spreadsheet Compare will still display and compare it.
• This side-by-side view allows you to visually scan for differences across worksheets and cells.

Color-Coded Differences

Spreadsheet Compare uses a color-coding system to highlight different types of changes.

• Fill Colors: Cells with “entered values” (data directly typed into a cell, not formulas) that have changed are often highlighted with a green fill color in the side-by-side grid.
• Font Colors: Changes in formulas or other types of modifications might be indicated by different font colors.
• Legend: Always refer to the legend in the lower-left pane to understand the specific meaning of each color used in the comparison. This legend clarifies what type of change each color represents (e.g., values entered, formulas changed, formatting differences).

Resize Cells for Better Visibility

Sometimes, cell content might be truncated in the grid view if the columns are too narrow.

1. If you find it difficult to read the full content of cells, click “Resize Cells to Fit” on the ribbon.

2. This will automatically adjust column widths to ensure you can see the complete cell contents, improving readability and making it easier to compare in Excel.

Exporting and Utilizing Comparison Data

Spreadsheet Compare offers options to export or copy the comparison results for further analysis or record-keeping.

Export Results to Excel

If you need to save the comparison results in a shareable and readable format, exporting to an Excel file is a great option.

1. Click “Home > Export Results” on the ribbon.
2. Spreadsheet Compare will create a new Excel file summarizing the comparison.
3. This exported file provides an easy-to-read overview of all the detected differences.

Copy Results to Clipboard

For quick sharing or integration with other documents (like a report in Microsoft Word), you can copy the results to the clipboard.

1. Click “Home > Copy Results to Clipboard“.
2. The comparison data is now copied.
3. You can paste this data into another application, such as Microsoft Word or Notepad.

Show Workbook Colors for Visual Fidelity

By default, Spreadsheet Compare might simplify the formatting for clarity. If you want to see a more accurate representation of the original workbook’s formatting in the comparison view:

1. Click “Home > Show Workbook Colors“.
2. This option displays the cell formatting from the original workbooks, providing a higher-fidelity visual representation of how the worksheets look in Excel.

Beyond Change Tracking: Other Applications

While primarily used for tracking changes, Spreadsheet Compare has other valuable applications when you compare in Excel.

Auditing and Compliance

For organizations undergoing audits, Spreadsheet Compare is invaluable.

• It helps create an audit trail of critical workbooks, showing changes over time (month-over-month or year-over-year).
• This trail aids in identifying and rectifying errors proactively before auditors discover them.
• It supports compliance efforts by providing clear documentation of workbook modifications.

Comparing VBA Code

Spreadsheet Compare isn’t limited to worksheet data; it can also compare in Excel VBA (Visual Basic for Applications) code.

• If your Excel workbooks contain macros, Spreadsheet Compare can identify differences in the VBA code between versions.
• The VBA comparison results are presented in a separate window, showing the code side-by-side with highlighted differences.
• This is crucial for managing and tracking changes in the functionality and logic embedded within your Excel workbooks.

Conclusion

Spreadsheet Compare is a robust and essential tool for anyone who needs to compare in Excel. Whether you’re tracking collaborative edits, ensuring data integrity across versions, preparing for audits, or managing VBA code, this feature provides a clear and efficient way to identify and understand changes in your Excel workbooks. By following this guide, you can leverage the power of Spreadsheet Compare to streamline your workflow and maintain control over your spreadsheet data.