Imagine you’re working with multiple Excel workbooks, perhaps different versions of a financial report, sales data, or project plans. Identifying the changes between these versions manually can be a tedious and error-prone task. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare, designed to pinpoint the exact differences between two Excel files, saving you time and ensuring accuracy. This guide will walk you through how to effectively use Spreadsheet Compare to Compare Two Excel Workbooks For Differences.
What is Spreadsheet Compare?
Spreadsheet Compare is a Microsoft tool designed to highlight the differences between two Excel workbooks. It goes beyond simply comparing cell values; it can identify changes in formulas, macros, cell formatting, and more. This is incredibly useful for:
- Version Control: Quickly see what has changed between different iterations of a workbook.
- Error Detection: Identify unintentional changes, broken formulas, or inconsistencies.
- Auditing: Track modifications made to important spreadsheets.
- Quality Assurance: Ensure data integrity by comparing datasets for discrepancies.
Important Note: Spreadsheet Compare is not a standalone application available with all versions of Excel. It is included with specific Microsoft Office suites, namely:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
Before proceeding, ensure you have one of these versions installed to access Spreadsheet Compare.
How to Open Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward. Here’s how to open it:
- Via the Start Menu: Click on the Start button in Windows.
- Type to Search: Begin typing “Spreadsheet Compare”.
- Select the Application: The “Spreadsheet Compare” application should appear in the search results. Click on it to launch.
If you cannot find it by searching, double-check that you have a compatible version of Office installed as listed above.
Step-by-Step Guide to Compare Excel Workbooks
Once you have Spreadsheet Compare open, follow these steps to compare your Excel files:
-
Initiate File Comparison: Click on the Home tab, and then select Compare Files.
Alt text: Compare Files command highlighted in the Home tab of Spreadsheet Compare.
This action will open the Compare Files dialog box.
-
Select the Original Workbook (Compare): In the Compare box, click the blue folder icon. This will open a file browser window. Navigate to and select the older or original 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.
-
Select the Modified Workbook (To): Next to the To box, click the green folder icon. Browse to and select the more recent or modified version of the workbook you want to compare against the original. Click OK after selecting the file.
Tip: You can effectively compare two files even if they have identical names, as long as they are saved in different folders. This is especially helpful when dealing with versioned files.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll see a list of options. These options allow you to specify what aspects of the workbooks you want to compare. You can select or deselect options such as:
- Formulas: Compares formulas in cells.
- Macros (VBA): Identifies differences in VBA code.
- Cell Format: Checks for changes in formatting like fonts, colors, and number formats.
- Values: Compares the values contained within cells.
- Worksheet Structure: Detects additions, deletions, or rearrangements of worksheets.
For a comprehensive comparison, you can simply click Select All. Choose the options that are most relevant to your comparison needs.
-
Run the Comparison: Click OK to initiate the comparison process. Spreadsheet Compare will analyze both workbooks based on your selected options.
Password Protected Workbooks: If either of the workbooks is password protected, you might receive an “Unable to open workbook” message. Click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can handle password-protected files, ensuring you can still compare them.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid.
-
Side-by-Side View: The left pane displays the “Compare” workbook (typically the older version), and the right pane shows the “To” workbook (usually the newer version).
-
Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons on the horizontal scroll bar located above the grids. 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. The color coding helps you quickly understand the nature of each change.
Alt text: Comparison results showing differences between two Excel workbook versions, with color-coded highlights for value and formula changes.
- Legend Pane: The pane located below the two grids provides a legend explaining what each color represents. For example, a green fill color often indicates “entered values” (values that are not derived from formulas) have been changed, while a blue-green fill might indicate changes in “calculated values” (formula results).
Example of Interpreting Results:
Consider a scenario where you are comparing two versions of a quarterly sales report. In the older version, the Q4 figures were preliminary. The updated version contains the final Q4 sales numbers.
In the comparison results:
-
Cells containing the updated Q4 sales figures in both versions might be highlighted with a green fill, indicating changed entered values.
-
Consequently, the “Year-to-Date” (YTD) totals, which are calculated based on quarterly figures, would also change. Cells with updated YTD values might be highlighted with a blue-green fill, signifying changed calculated values.
-
Spreadsheet Compare will highlight not just value changes, but also formula corrections. If a formula was incorrect in the older version and has been fixed in the newer one, this change will also be clearly indicated.
-
Resize Cells: If cell contents are truncated due to column width, click Resize Cells to Fit to automatically adjust column widths for better readability.
Excel’s Inquire Add-in: An Alternative Tool
In addition to Spreadsheet Compare, Excel 2013 and later versions offer an “Inquire” add-in. This add-in provides a range of analytical tools within Excel itself, including the ability to compare files. To use the file comparison feature within Excel:
- Enable the Inquire Add-in: If you don’t see the “Inquire” tab in Excel, you may need to activate the add-in through Excel Options.
- Use the Compare Files Command: Once enabled, the “Inquire” tab will appear in the Excel ribbon. Within this tab, you will find a “Compare Files” command that essentially launches Spreadsheet Compare directly from within Excel, streamlining the comparison process if you are already working in Excel.
While Spreadsheet Compare is a dedicated application, the Inquire add-in offers a convenient way to access similar comparison functionality from within Excel.
Conclusion
Spreadsheet Compare is an invaluable tool for anyone who works with Excel workbooks and needs to track changes, identify discrepancies, or ensure data accuracy. By following the steps outlined in this guide, you can efficiently compare two Excel workbooks for differences, saving time and improving the quality of your spreadsheet-based work. Whether you are managing financial data, project plans, or any other critical information in Excel, mastering Spreadsheet Compare will significantly enhance your workflow.
