Do you need to identify the discrepancies between two Excel spreadsheets? Whether you’re working with different versions of the same workbook or comparing data from separate sources, finding differences manually can be time-consuming and prone to errors. Microsoft Spreadsheet Compare offers a robust solution to efficiently compare Excel files and pinpoint exactly what has changed.
This guide will walk you through the process of using Spreadsheet Compare to effectively analyze and compare two Excel sheets for differences. We’ll cover everything from opening the tool to understanding the comparison results, ensuring you can quickly identify modifications, inconsistencies, and potential issues within your spreadsheets.
Getting Started with Spreadsheet Compare
Microsoft Spreadsheet Compare is a powerful tool designed to highlight the differences between Excel workbooks. It’s important to note that this utility is included with specific Microsoft Office suites: Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you have one of these versions, you already have access to Spreadsheet Compare.
To launch the application, follow these simple steps:
- Access the Start Menu: Click on the Start button in Windows.
- Find Spreadsheet Compare: Look for Spreadsheet Compare in your list of applications. If you don’t see it immediately, start typing “Spreadsheet Compare.”
- Open the Application: Select Spreadsheet Compare from the search results to open the program.
Once opened, Spreadsheet Compare presents a user-friendly interface ready to assist you in your Excel sheet comparison tasks.
Step-by-Step Guide to Compare Excel Sheets
Comparing two Excel workbooks for differences is straightforward with Spreadsheet Compare. Follow these steps to initiate a comparison:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the Original Workbook: In the Compare box, click the blue folder icon. This prompts a browse window to open. Locate and select the earlier version or the first Excel sheet you want to compare. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the Modified Workbook: Next, click the green folder icon located next to the To box. Browse to and select the second Excel workbook you want to compare against the first one. Click OK to confirm your selection.
-
Customize Comparison Options: Before running the comparison, you have the option to specify what aspects of the workbooks you want to examine for differences. In the left pane of the Compare Files dialog, you’ll find checkboxes for various elements such as Formulas, Macros, and Cell Format. You can select specific options or choose Select All to compare everything.
-
Run the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze both Excel sheets based on your selected options.
If either of the workbooks is password-protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and enter the password for the respective workbook when prompted. Spreadsheet Compare is designed to handle password-protected files, allowing you to compare them securely.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid. The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer version). A detailed pane below the grids provides a legend and further information about the detected differences.
-
Side-by-Side Worksheet Comparison: Each worksheet in the compared workbooks is analyzed against its counterpart. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located above the grid. 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. For instance, cells containing directly entered values (not formulas) that have been modified are typically highlighted with a green fill color in the grid and a green font in the results list. The legend in the lower-left pane clearly explains the meaning of each color code.
Consider an example where a quarterly report is updated. The initial version (left pane) might have preliminary figures for Q4, while the updated version (right pane) contains finalized data. In the comparison results, cells in the ‘Q4’ column with revised entered values will be highlighted in green. Consequently, any calculated fields that depend on these values, such as ‘Year-to-Date’ totals, will also be highlighted, often in a different color like blue-green, indicating a change in calculated values.
Furthermore, Spreadsheet Compare can detect formula changes. If a formula has been corrected or altered between versions, this difference will also be highlighted, allowing you to identify not only data changes but also modifications to the underlying calculations.
- Resize Cells for Better Visibility: If cell content is truncated due to column width, click Resize Cells to Fit. This option automatically adjusts column widths to ensure you can view the complete content of all cells, making it easier to analyze the differences.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the “Inquire” add-in, which provides a range of analytical tools. To enable it, you may need to activate it within Excel’s options. Once enabled, an “Inquire” tab appears in the Excel ribbon.
The Inquire add-in offers features to:
- Analyze Workbooks: Gain insights into workbook structure, formulas, and potential errors.
- Visualize Relationships: See connections between cells, worksheets, and even external workbooks.
- Clean Excess Formatting: Optimize workbooks by removing unnecessary formatting that can slow them down.
While Spreadsheet Compare is dedicated to comparing workbooks side-by-side, the Inquire add-in provides a broader suite of tools for workbook analysis and auditing. You can even launch Spreadsheet Compare directly from Excel using the Inquire add-in to quickly compare two currently open workbooks.
Taking Excel Comparison Further
For organizations managing critical Excel workbooks and Access databases, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server provide comprehensive change management, inventory, and risk assessment features specifically designed for user-developed tools in Excel and Access. These server-based solutions offer a more enterprise-grade approach to managing and controlling spreadsheet environments.
To learn more about Spreadsheet Compare and its capabilities, refer to the Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare, you can significantly streamline the process of Comparing 2 Excel Sheets For Differences, ensuring data accuracy, identifying critical changes, and maintaining the integrity of your spreadsheets.
