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 workbooks manually can be a tedious and error-prone task. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare to streamline this process. This guide will walk you through how to effectively use Spreadsheet Compare to pinpoint the exact differences between your Excel files, saving you time and ensuring accuracy in your data analysis.
Who Can Use Spreadsheet Compare?
Before we dive into the steps, it’s important to note the availability of Spreadsheet Compare. This tool is not included in all versions of Microsoft Office. You’ll need one of the following to access Spreadsheet Compare:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you’re unsure which version you have, check your Office suite information or consult your IT department. If you have a compatible version, let’s get started on comparing your Excel workbooks.
Step-by-Step Guide: Comparing Two Excel Workbooks
Spreadsheet Compare offers a straightforward process to analyze and highlight the distinctions between two Excel files. Follow these steps to compare your workbooks:
1. Open Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application.
- For Windows 10 and later: Click the Start button, then type “Spreadsheet Compare”. You should see “Spreadsheet Compare” appear in the search results. Click on it to open the application.
- For older Windows versions: Go to the Start screen and look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare” and select it from the options.
You might also notice “Database Compare” alongside Spreadsheet Compare. Database Compare is a similar tool for Access databases, but for this guide, we’ll focus solely on Excel workbooks.
2. Select Files for Comparison
Once Spreadsheet Compare is open, you 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 version or the first workbook you want to compare. Navigate to the file location on your computer or network. You can even input a web address if your workbooks are stored online. Select the file and click Open.
-
Next, click the green folder icon next to the To box. Browse to and select the newer version or the second workbook you want to compare against the first one. Click Open and then OK in the Compare Files dialog box.
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 saved in different project folders.
3. Choose Comparison Options
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare should analyze.
-
In the left pane of the Compare Files dialog box, you’ll see a list of options. These options let you specify which elements to include in the comparison report. You can choose to compare:
- Formulas: Highlights changes in formulas.
- Macros (VBA Code): Identifies differences in VBA macros.
- Cell Format: Shows changes in cell formatting (fonts, colors, etc.).
- Values: Compares the values within cells.
- Worksheet Names: Checks for changes in worksheet names.
-
Check the boxes next to the options you want to include in the comparison. If you want to compare everything, simply click Select All.
-
Click OK to initiate the workbook comparison.
4. Analyze the Results
After you click OK, Spreadsheet Compare will process the workbooks and display the comparison results in a clear, side-by-side grid.
-
Side-by-Side Grid: The results are presented in a two-pane view. The workbook you selected in the “Compare” box (typically the older version) is shown on the left, and the “To” workbook (typically the newer one) is on the right. Each worksheet from both workbooks is compared against its counterpart in the other file. You can navigate between worksheets using the forward and back buttons on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Highlighting: Differences are visually highlighted using different colors within the grid. The color coding helps you quickly identify the type of change. For instance, cells containing values that were directly entered (not calculated by a formula) and have changed are often highlighted with a green fill.
-
Details Pane: Below the side-by-side grid, a details pane provides a legend explaining the color codes and may offer more specific information about the changes.
Understanding Comparison Results in Detail
Let’s delve deeper into interpreting the comparison results. The color-coding is key to quickly grasping the types of changes detected by Spreadsheet Compare.
Consider the example above. If you see:
- Green Fill: This often indicates “entered values” have changed. This means a cell that previously contained a manually typed number or text now has a different manually typed value. In the example, cells E2:E5 show a green fill, signifying that the quarterly figures (entered values) have been updated.
- Blue-Green Fill: This frequently points to changes in “calculated values.” This means a formula cell’s result has changed. This could be due to changes in input values (like the green-filled cells) or modifications to the formula itself. In the example, cells F2:F6 with blue-green fill show that the year-to-date (YTD) calculated results have changed, likely due to the updated quarterly figures.
In cell F5 of the example, a formula correction is also highlighted. The original formula was incorrect, summing only B5:D5. The updated version correctly sums B5:E5. Spreadsheet Compare not only shows value changes but can also reveal formula errors that have been rectified.
- Resize Cells to Fit: If cell contents are truncated due to column width, click Resize Cells to Fit to expand columns and view the full content.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers an “Inquire” add-in (available in Excel 2013 and later, in some Office versions) that provides further workbook analysis capabilities. The Inquire add-in, once enabled, adds an “Inquire” tab to your Excel ribbon.
From the Inquire tab, you can:
- Analyze Workbook: Get a comprehensive overview of workbook structure, formulas, and potential issues.
- Workbook Relationship: Visualize the connections between cells, worksheets, and even links to other workbooks.
- Clean Excess Cell Formatting: Remove unnecessary formatting that can bloat file size and impact performance.
While Spreadsheet Compare is excellent for direct file comparison, the Inquire add-in offers broader workbook analysis tools within Excel itself. To learn more about enabling and using the Inquire add-in, refer to Microsoft’s guide on how to turn on the Inquire add-in. You can also explore the capabilities of Spreadsheet Inquire for more in-depth analysis.
Conclusion: Enhance Your Excel Workflow with Workbook Comparison
Spreadsheet Compare is a valuable tool for anyone working with multiple Excel workbooks or managing different versions of important spreadsheets. By quickly and accurately highlighting differences, it simplifies tasks like version control, auditing, and error detection. Whether you’re tracking financial data, managing project details, or collaborating on spreadsheets, mastering workbook comparison techniques will significantly enhance your efficiency and data accuracy in Excel. For organizations managing critical Excel files, consider exploring Microsoft’s enterprise-level solutions like Microsoft Audit and Control Management Server for even more robust change management and risk mitigation features.
