Comparing two Excel spreadsheets to identify differences can be a common yet crucial task for professionals in various fields. Whether you’re managing budgets, tracking sales data, or auditing financial records, pinpointing discrepancies between versions of a spreadsheet is essential for accuracy and informed decision-making. Microsoft Spreadsheet Compare offers a robust solution to efficiently highlight these differences, saving you time and reducing the risk of overlooking critical changes.
What is Spreadsheet Compare?
Microsoft Spreadsheet Compare is a powerful tool designed to compare two Excel workbook files and generate a detailed report of their differences. This utility is particularly useful when you need to audit changes, ensure data consistency across versions, or troubleshoot discrepancies. It goes beyond simply highlighting cell value changes; Spreadsheet Compare can identify differences in formulas, macros, cell formatting, and more.
Important Note: It’s worth noting that Spreadsheet Compare is not a standalone application readily available in all Office suites. It is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a different version of Excel, you may need to check your Office suite to confirm availability.
How to Open Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward if you have a compatible version of Office installed. Here’s how to open it:
- Via the Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: Begin typing “Spreadsheet Compare”. As you type, the search results will filter.
- Select Spreadsheet Compare: Click on the Spreadsheet Compare option when it appears in the search results to launch the application.
Opening the Compare Files dialog box in Microsoft Spreadsheet Compare, ready to select Excel workbooks for comparison.
Once opened, you’ll notice a clean and intuitive interface ready to facilitate your spreadsheet comparisons.
Step-by-Step Guide to Compare Two Excel Workbooks
Let’s walk through the process of comparing two Excel files using Spreadsheet Compare:
Launch Compare Files
- Navigate to Home Tab: In the Spreadsheet Compare window, ensure you are on the Home tab.
- Click Compare Files: Click the Compare Files button. This action will open the Compare Files dialog box, which is your starting point for selecting the spreadsheets you want to analyze.
Select the Files to Compare
- Choose the Original File: In the Compare box, click the blue folder icon. This prompts a browse window to open. Navigate to and select the earlier version or the first Excel workbook you wish to compare. You can select files from your local computer, network locations, or even enter a web address if your files are stored online.
- Choose the Modified File: Next, in the To box, click the green folder icon. Again, a browse window will appear. Locate and select the later version or the second Excel workbook that you want to compare against the first one. Click OK after selecting the file.
Tip: You can efficiently compare files even if they have identical names, as long as they are saved in different folders. This is helpful for comparing versions saved at different times or in different project directories.
Choose Comparison Options
Before running the comparison, you have the option to specify what types of differences you want Spreadsheet Compare to focus on:
- Review Options: In the left pane of the Compare Files dialog box, you’ll see a list of options. These include Formulas, Macros, Cell Format, and others.
- Customize Comparison: Check the boxes next to the elements you want to include in the comparison. For a comprehensive comparison, you can click Select All. If you are only interested in specific aspects, such as formula changes, select only the relevant options.
Run the Comparison
- Initiate Comparison: Once you have selected your files and chosen your comparison options, click the OK button in the Compare Files dialog box.
- Enter Passwords if Required: If either of the Excel workbooks is password-protected, you might receive an “Unable to open workbook” message. Click OK and enter the password(s) for the protected file(s) when prompted. Spreadsheet Compare needs to access the content of the files to perform the comparison.
After initiating the comparison, Spreadsheet Compare will analyze the two workbooks based on your selected options. The results will then be displayed in a clear, side-by-side grid within the application window.
Understanding the Comparison Results
Spreadsheet Compare presents the comparison results in a user-friendly format, making it easy to understand the differences between your Excel files:
-
Side-by-Side Grid: The results are displayed in a two-pane grid. The left pane shows the “Compare” file (typically the older version), and the right pane shows the “To” file (typically the newer version). Each pane displays the worksheets from the respective workbooks. You can navigate through different worksheets using the scroll bar buttons if your workbooks contain multiple sheets.
-
Color-Coded Highlighting: Differences are highlighted with colors to quickly distinguish the types of changes. For example, cells containing changed “entered values” (values not derived from formulas) might be highlighted with a green fill. Cells with changed calculated values (formulas) might be highlighted with a different color, such as blue-green.
-
Legend Pane: A legend is usually displayed in the lower-left pane of the Spreadsheet Compare window. This legend is crucial for interpreting the color-coding. It explains what each color represents, helping you quickly understand the nature of the differences found.
Side-by-side comparison results in Microsoft Spreadsheet Compare, highlighting value and formula changes between two Excel workbook versions.
For instance, in the example above, you can see how changes in manually entered values in columns E (Q4) lead to changes in calculated values in column F (YTD). The color-coding clearly differentiates between these types of changes, allowing for quick analysis.
- Resize Cells for Better View: If cell contents are truncated due to column width, click the Resize Cells to Fit option. This adjusts the column widths to ensure you can see the complete content of each cell, which is particularly useful for reviewing text-heavy cells or complex formulas.
Explore Excel’s Inquire Add-in
While Spreadsheet Compare is a dedicated tool, Excel itself offers an Inquire add-in, available in some versions, which provides additional analytical capabilities. Once enabled, the Inquire add-in adds an “Inquire” tab to your Excel ribbon.
From the Inquire tab, you can perform workbook analysis, visualize relationships between cells and worksheets, and even clean up excessive formatting in your spreadsheets. If you already have two workbooks open in Excel and want to compare them using Spreadsheet Compare, the Inquire add-in provides a convenient way to launch the comparison directly from within Excel using the “Compare Files” command.
If you don’t see the Inquire tab in your Excel, you may need to activate it through Excel Options > Add-Ins > Manage: “COM Add-ins” > Go > check “Inquire” > OK.
Next Steps
For organizations heavily reliant on Excel workbooks and Access databases for critical operations, consider exploring Microsoft’s comprehensive management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server offer advanced change management, inventory, and analysis features designed to mitigate risks associated with end-user developed tools in Excel and Access environments.
Spreadsheet Compare provides an effective and efficient method to Compare 2 Excel Spreadsheets For Differences. By leveraging this tool, you can maintain data integrity, track changes accurately, and ensure the reliability of your spreadsheet-based information.
