Do you need to compare two Excel workbooks to pinpoint the differences? Whether you’re managing versions of the same file, auditing for errors, or simply trying to understand what has changed, identifying discrepancies can be a time-consuming manual task. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare that can automate this process, highlighting the differences between two Excel files with ease.
Important Note: Spreadsheet Compare is not available in all versions of Excel. It is included with 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 utilize this feature.
Getting Started with Spreadsheet Compare
First, you need to open the Spreadsheet Compare application. It’s a standalone program, not directly within Excel itself.
-
Accessing Spreadsheet Compare: Go to the Start Menu in Windows.
-
Locate the Application: Look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare” and it should appear as a search result. Click on it to open.
This image demonstrates how to locate and open the Spreadsheet Compare application from the Windows Start Menu by searching for it.
Alongside Spreadsheet Compare, you might also find Database Compare, a similar tool for Access databases, which is also part of the Office Professional Plus suite and Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare your Excel files and highlight the differences:
-
Initiate Comparison: Click on Home > Compare Files. This action will open the Compare Files dialog box.
-
Select the Older Workbook (Compare): In the Compare Files dialog box, locate the Compare field. Click the blue folder icon next to it. This will allow you to browse your files. Navigate to and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Select the Newer Workbook (To): Next, find the To field and click the green folder icon beside it. Browse to and select the more recent version of the workbook that you want to compare against the older one. Click OK to confirm your selection.
This image shows the “Compare Files” dialog box in Microsoft Spreadsheet Compare, highlighting the blue and green folder icons used to select the workbooks for comparison.
Tip: You can even compare two files with identical names as long as they are saved in different folders.
-
Choose Comparison Options: In the left pane of the Compare Files dialog, you’ll find a list of options to customize your comparison. You can select or deselect options like Formulas, Macros, Cell Format, and more. To compare everything, simply check Select All. Choose the options that are relevant to your comparison needs.
-
Run the Comparison: Click OK to start the comparison process.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password-protected. Click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, but you’ll need to provide the password to access them.
Understanding the Highlighted Comparison Results
After running the comparison, Spreadsheet Compare will display the results in a clear, two-pane grid.
-
Side-by-Side View: The workbook you selected as “Compare” (older version) is shown on the left pane, and the “To” workbook (newer version) is on the right pane.
-
Worksheet Navigation: If your workbooks have multiple worksheets, you can navigate between them using the forward and back buttons on the horizontal scroll bar located above the grids. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences between the workbooks. The cell fill color or text font color will change based on the type of modification. For instance, cells containing directly entered values (not formulas) that have been modified are often highlighted with a green fill color in the grid and green font in the details pane below. A legend in the lower-left pane explains the meaning of each color code.
This image illustrates the comparison results in Spreadsheet Compare, showcasing the side-by-side grid with color-coded highlights indicating different types of changes between two Excel workbook versions.
In the example above, you can see that direct values in column E (Q4) have been updated (green fill). Consequently, calculated values in the YTD column (F) have also changed (blue-green fill). Furthermore, cell F5 shows a formula correction in the newer version, indicated by a different highlight.
-
Resize Cells: If cell contents are truncated due to narrow columns, click Resize Cells to Fit to expand column widths and view the full content.
Explore the Inquire Add-in in Excel
Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in. This add-in, once activated, provides an “Inquire” tab within Excel, offering additional analytical capabilities. From the Inquire tab, you can analyze workbook structure, visualize relationships between cells and worksheets, and even clean up excessive formatting.
While Spreadsheet Compare is a dedicated tool for comparing files externally, the Inquire add-in enhances Excel’s built-in analysis features. You can even launch Spreadsheet Compare directly from Excel using the Inquire add-in to compare currently open workbooks.
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”.
Conclusion: Streamline Excel Workbook Comparison
Spreadsheet Compare is a valuable tool for anyone who works with multiple versions of Excel workbooks or needs to audit spreadsheets for changes and errors. By quickly and visually highlighting differences, it saves significant time and effort compared to manual comparison. Whether you are tracking changes, ensuring data integrity, or troubleshooting formula issues, leveraging Spreadsheet Compare can greatly improve your efficiency and accuracy when working with Excel files. For organizations managing critical Excel workbooks, consider exploring Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server for enhanced change management and risk mitigation.
