Comparing two Excel spreadsheets can become a necessary task for various reasons. Whether you’re managing different versions of a financial report, auditing data entries, or simply trying to consolidate information, identifying the discrepancies between two Excel files is crucial. Microsoft Excel offers a built-in tool called Spreadsheet Compare designed to highlight these differences efficiently. This guide will walk you through how to use this powerful utility to compare your spreadsheets and pinpoint exactly what has changed.
Important Note: Spreadsheet Compare is 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. If you’re using a standard version of Excel, you may need to explore alternative methods or consider upgrading to a suite that includes this feature.
Accessing Spreadsheet Compare
First, you need to locate and open the Spreadsheet Compare tool. The method to do this depends slightly on your Windows version, but here’s a general approach:
- Via the Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: Begin typing “Spreadsheet Compare”. As you type, the search function will filter results.
- Select Spreadsheet Compare: You should see “Spreadsheet Compare” appear as an option. Click on it to launch the application.
If you don’t immediately find it, ensure you have one of the compatible Office Professional Plus or Microsoft 365 Apps for enterprise versions installed.
Alongside Spreadsheet Compare, you might notice Database Compare, a similar tool for Microsoft Access databases. Both are valuable for professionals working extensively with data management within the Microsoft ecosystem.
Step-by-Step Guide: Comparing Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare two Excel workbooks:
-
Initiate File Comparison: Click on Home > Compare Files. This action will open the Compare Files dialog box, which is your control center for selecting the spreadsheets you want to analyze.
-
Select the “Compare” Workbook (Older Version): In the Compare Files dialog, locate the Compare box. Click the blue folder icon situated next to it. This prompts a file explorer window to open, allowing you to browse your computer or network for the earlier version of your Excel workbook. You can also input a web address if your files are stored online.
-
Select the “To” Workbook (Newer Version): Next, find the To box and click the green folder icon beside it. Again, a file explorer window will appear. This time, navigate to and select the more recent version of the workbook you want to compare against the older one. Click OK after selection.
Pro Tip: It’s perfectly acceptable to compare two files that have identical names, as long as they are saved in different folders. Spreadsheet Compare differentiates them based on their file paths.
-
Choose Comparison Options: Look at the left pane within the Compare Files dialog. Here, you’ll find a list of elements you can include or exclude from the comparison report. Options include Formulas, Macros, Cell Format, and more. You can choose specific options or simply click Select All to compare everything.
-
Execute the Comparison: Finally, click OK to initiate the comparison process. Spreadsheet Compare will analyze both workbooks based on your selected options.
Password Protected Workbooks: If either workbook is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and analyze the file content.
Interpreting the Comparison Results
After running the comparison, the results are displayed in a clear, two-pane grid interface.
-
Side-by-Side Grid: The left pane displays the “Compare” workbook (typically the older file), and the right pane shows the “To” workbook (usually the newer one). Each worksheet within these workbooks is compared against its counterpart in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located at the bottom of the grid.
Important Note: Even worksheets that are hidden within the Excel files are included in the comparison and will be displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences it detects. Changes are visually distinct, making it easy to quickly grasp the modifications. For instance, cells containing “entered values” (values that are not results of formulas) that have been modified are typically highlighted with a green fill color in the grid and a green font in the detailed results list below.
-
Legend Pane: Located beneath the two grids, the legend pane is crucial for understanding the color codes. It provides a key that explains what each color signifies in terms of the type of change detected. Refer to this legend to accurately interpret the highlighted differences.
Example Scenario: Imagine you are comparing quarterly sales reports. An earlier version (left pane) might have preliminary figures for Q4. The updated version (right pane) contains finalized numbers for Q4. In the comparison results:
-
Cells in the Q4 columns (e.g., column E) that contain the updated sales figures will likely have a green fill, indicating a changed “entered value.”
-
Consequently, if there are formulas calculating year-to-date (YTD) totals (e.g., column F), these cells might show a blue-green fill. This signifies a “calculated value” change, resulting from the updated Q4 figures.
-
You might even discover formula errors. In our example, perhaps a YTD formula in the older version was incorrectly summing only Q1-Q3 data. The updated version could have a corrected formula. Spreadsheet Compare would highlight this formula change, bringing attention to potential calculation issues.
-
Resize Cells for Better Viewing: If cell contents are truncated due to column width, click Resize Cells to Fit. This feature automatically adjusts column widths to ensure you can see the full content of each cell within the comparison grid, improving readability.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer another valuable tool: the Inquire add-in. This add-in provides an “Inquire” tab in Excel’s ribbon, offering a suite of analytical features.
From the Inquire tab, you can:
- Analyze Workbooks: Gain deeper insights into workbook structure, identify potential errors, and understand dependencies.
- Visualize Relationships: See the connections between cells, worksheets, and even links to external workbooks.
- Clean Excess Formatting: Remove unnecessary formatting that can bloat file size and impact performance.
If you have workbooks open in Excel and need to compare them using Spreadsheet Compare, you can conveniently launch Spreadsheet Compare directly from Excel via the Inquire add-in using the “Compare Files” command within the Inquire tab.
Enabling the Inquire Add-in: If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. Go to File > Options > Add-Ins. In the “Manage” dropdown at the bottom, select “COM Add-ins” and click “Go…”. In the COM Add-Ins dialog, check the box next to “Inquire Add-in” and click “OK”. The Inquire tab should now appear in your Excel ribbon.
Taking Data Management Further
For organizations heavily reliant on “mission-critical” Excel workbooks or Access databases, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides robust change management capabilities for these file types. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features. These server solutions are designed to help mitigate risks associated with user-developed tools in Excel and Access by providing centralized control and oversight.
Further Resources: For a broader overview of Spreadsheet Compare, refer to the Overview of Spreadsheet Compare resource for more in-depth information.
By utilizing Spreadsheet Compare and the Inquire add-in, you can effectively compare Excel spreadsheets, understand the differences, and maintain data integrity across your workbooks. These tools are invaluable for anyone working with and managing Excel data regularly.
