In today’s data-driven world, working with spreadsheets is a common task. Often, you might find yourself needing to compare two Excel workbooks or different versions of the same file to track changes, identify errors, or ensure data consistency. Whether you’re auditing financial data, managing project timelines, or simply collaborating on a spreadsheet, knowing how to effectively Compare Sheets In Excel is an invaluable skill.
Microsoft Excel offers a built-in tool called Spreadsheet Compare that can help you analyze and report on the differences between Excel files. This guide will walk you through how to use Spreadsheet Compare to effectively compare sheets in Excel and understand the results.
Important Note: Spreadsheet Compare is a powerful utility, but it’s exclusively available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to access this feature.
Accessing Spreadsheet Compare
To begin comparing sheets in Excel, you first need to open the Spreadsheet Compare tool. Here’s how:
- Navigate to your Start Menu in Windows.
- Look for Spreadsheet Compare. If you don’t immediately see it, start typing “Spreadsheet Compare,” and it should appear in the search results.
- Click on Spreadsheet Compare to launch the application.
Once opened, Spreadsheet Compare presents a straightforward interface designed for comparing files efficiently. Besides comparing Excel spreadsheets, Microsoft also provides a similar tool called Database Compare for Access databases, included with the same Office Professional Plus and Microsoft 365 Apps for enterprise versions.
Step-by-Step Guide to Compare Excel Workbooks
Spreadsheet Compare simplifies the process of comparing sheets in Excel. Follow these steps to effectively analyze differences between two workbooks:
-
Initiate Comparison: In the Spreadsheet Compare window, click Home > Compare Files. This action will open the Compare Files dialog box.
-
Select Workbooks:
- In the Compare box, click the blue folder icon. This allows you to browse and select the earlier version of your Excel workbook. You can choose files from your local computer, network locations, or even enter a web address if your workbooks are stored online.
- Next, in the To box, click the green folder icon. Browse to and select the more recent version of the workbook you want to compare against the earlier one. Click OK after selecting the files.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, provided they are saved in different folders. This is particularly useful when dealing with version-controlled documents.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options to customize your comparison. You can select or deselect options like Formulas, Macros, Cell Format, and others to specify what aspects of the sheets you want to compare. To compare everything, simply click Select All.
-
Run the Comparison: Once you have selected your workbooks and comparison options, click OK to start the comparison process.
-
Password Protected Files: 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 protected workbook when prompted. Spreadsheet Compare is designed to work with password-protected files, ensuring you can still compare sheets in Excel even with security measures in place.
After running the comparison, Spreadsheet Compare will display 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 summary of the changes identified. Differences are visually highlighted using colors, making it easy to pinpoint exactly what has changed between the two versions.
Understanding the Comparison Results
Interpreting the results is crucial to effectively compare sheets in Excel using Spreadsheet Compare. Here’s how to understand the visual cues and information presented:
-
Side-by-Side Worksheet Comparison: The main grid displays worksheets from both files side by side. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons located on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and shown in the results, ensuring a comprehensive analysis.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight different types of changes:
- Green Fill Color: Indicates cells containing “entered values” (non-formula cells) that have been modified. In the results pane, these changes are also often indicated with green font.
- Blue-Green Fill Color: Highlights cells where “calculated values” have changed. This usually means that a formula’s result has been affected by changes in input cells.
-
Legend Pane: The lower-left pane of the Spreadsheet Compare window acts as a legend, explaining what each color code signifies. Refer to this pane to quickly understand the types of differences highlighted in the grid.
For example, if you see a green-filled cell, it means the value in that cell was directly changed. A blue-green filled cell suggests a change in a formula’s output, possibly due to alterations in other cells that the formula depends on.
- Resize Cells for Better Viewing: If cell contents are truncated due to narrow column widths, click Resize Cells to Fit. This option adjusts column widths to ensure you can see the complete content of each cell, making it easier to review the comparison results.
Leveraging Excel’s Inquire Add-in for Sheet Analysis
Beyond Spreadsheet Compare, Excel 2013 and later versions offer another powerful tool: the Inquire add-in. While Spreadsheet Compare is a standalone application, Inquire is integrated directly into Excel as an add-in.
To utilize Inquire, you first need to activate it. If you don’t see the “Inquire” tab in your Excel ribbon, you’ll need to enable it. Once activated, the Inquire add-in provides a range of analytical capabilities directly within Excel. From the “Inquire” tab, you can:
- Analyze Workbook: Gain insights into the structure, formulas, and potential issues within a workbook.
- Show Workbook Relationships: Visualize the connections between cells, worksheets, and even links to other workbooks. This is particularly useful for understanding complex spreadsheets.
- Clean Excess Cell Formatting: Optimize your spreadsheets by removing unnecessary formatting that can slow down performance and increase file size.
If you have two Excel workbooks open and want to use Spreadsheet Compare, you can also launch it directly from Excel using the “Compare Files” command within the Inquire add-in, if enabled.
Next Steps for Advanced Excel Management
For organizations that heavily rely on “mission-critical” Excel workbooks or Access databases, Microsoft offers more advanced management and auditing tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server provide robust features for change management, inventory, and risk assessment. These server-based solutions are designed to help reduce risks associated with user-developed tools in Excel and Access, ensuring data integrity and compliance.
To further explore the capabilities of Spreadsheet Compare, you can also refer to the Overview of Spreadsheet Compare for more detailed information and resources.
By using Spreadsheet Compare and the Inquire add-in, you can effectively compare sheets in Excel, identify changes, and maintain the integrity of your important spreadsheet data. These tools are essential for anyone working with Excel in a professional environment, helping to ensure accuracy and efficiency in data management and analysis.
