Imagine you’re working with tons of data in Excel, managing budgets, sales figures, or project plans. You’ve made revisions, sent versions back and forth, and now you’re faced with a common problem: figuring out exactly what changed between two Excel sheets. Manually going cell by cell is a nightmare and prone to errors. Thankfully, Microsoft provides a built-in tool called Spreadsheet Compare to make this task much easier and more efficient. This guide will walk you through how to use Spreadsheet Compare to quickly identify the differences between two Excel files, saving you time and ensuring accuracy.
Before we dive in, it’s important to note that Spreadsheet Compare is included with specific Microsoft Office suites, namely Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Office, you may need to check if you have access to this tool.
Opening Spreadsheet Compare
Let’s get started by opening the Spreadsheet Compare tool. The way to access it is straightforward:
- Go to your Start Menu in Windows.
- Look for Spreadsheet Compare. You can scroll through your apps or simply start typing “Spreadsheet Compare” to search for it.
- Click on Spreadsheet Compare to launch the application.
Once opened, you’ll be ready to compare your Excel files. Alongside Spreadsheet Compare, you might notice Database Compare for Access databases, which is a similar tool for comparing Access files, also part of the Office Professional Plus suite.
Step-by-Step Guide to Compare Two Excel Workbooks
Now, let’s walk through the process of comparing two Excel workbooks for differences using Spreadsheet Compare:
-
Initiate the 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.
Alt text: Compare Files button highlighted in the Home tab of Spreadsheet Compare.
-
Select the Original File: In the Compare Files dialog box, you’ll see two boxes labeled Compare and To. Click the blue folder icon next to the Compare box. This is where you’ll select the earlier version of your Excel workbook that you want to use as the basis for comparison. You can browse files on your computer, network locations, or even enter a web address if your files are stored online.
-
Select the Revised File: Next, click the green folder icon next to the To box. Browse to and select the more recent version of the Excel workbook that you want to compare against the original. Click OK after selecting the file.
Tip: You can easily compare two files even if they have the same name, as long as they are saved in different folders.
-
Choose Comparison Options: On the left side of the Compare Files dialog box, you’ll find a pane with various options. Here, you can specify what aspects of the workbooks you want to compare. You can choose to compare Formulas, Macros, Cell Formats, and more. For a comprehensive comparison, you can simply check the Select All option.
-
Run the Comparison: Once you’ve selected your files and comparison options, click OK to start the comparison process.
If you encounter an “Unable to open workbook” message, it’s likely that one of your Excel files is password protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file content.
After running the comparison, the results will be displayed in a split-screen grid, making it easy to visually identify differences.
Understanding the Comparison Results
The comparison results are presented in a clear, side-by-side layout. The workbook you selected as the “Compare” file (typically the older version) is shown on the left, and the “To” file (usually the newer one) is on the right. Below the grids, a details pane provides further information about the changes.
-
Worksheet Comparison: If your Excel files contain multiple worksheets, Spreadsheet Compare compares corresponding worksheets in each file. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Differences are highlighted with colors in the grid. The color indicates the type of change. For instance, cells containing values that were directly entered (not formulas) and have been modified are highlighted with a green fill color in the grid and a green font in the results list. A legend in the lower-left pane explains what each color represents, making it easy to interpret the changes.
Looking at the example above, you can see how changes are visually represented. In this scenario, the Q4 figures were updated in the newer version. Cells E2 to E5, which contain entered values that have changed, are marked with a green fill. Consequently, the calculated “YTD” values in column F, which depend on these changed figures, are also updated and highlighted with a blue-green fill, indicating a changed calculated value. Furthermore, cell F5 reveals a formula correction in the updated workbook, changing from
=SUM(B5:D5)to the correct=SUM(B5:E5). -
Resize Cells for Better View: If the cell content is too long to display within the cell width, you can click Resize Cells to Fit to automatically adjust column widths for better readability.
Exploring the Inquire Add-in in Excel
Beyond Spreadsheet Compare, Excel 2013 and later versions offer an Inquire add-in. Once enabled, it adds an “Inquire” tab to your Excel ribbon. This add-in provides a range of analytical tools. From the Inquire tab, you can analyze workbooks for potential issues, visualize relationships between cells and worksheets, and even clean up excessive formatting within a worksheet. If you already have two Excel files open and want to quickly compare them, the Inquire add-in provides a shortcut to launch Spreadsheet Compare directly from Excel using the “Compare Files” command.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it in Excel’s options. You can search for “Turn on the Inquire add-in” in Excel help for step-by-step instructions. To learn more about the capabilities of the Inquire add-in, search for “What you can do with Spreadsheet Inquire” in Excel help.
Conclusion
Spreadsheet Compare is a powerful and invaluable tool for anyone who needs to compare Excel sheets to identify differences. Whether you’re tracking changes across versions, auditing data, or ensuring formula accuracy, this tool streamlines the process and reduces the risk of manual errors. By following these steps, you can effectively use Spreadsheet Compare to maintain data integrity and improve your workflow when working with multiple Excel workbooks.
For organizations managing critical Excel files and Access databases, Microsoft offers advanced management tools like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These solutions provide enhanced change management, inventory, and analysis features to further mitigate risks associated with user-developed tools in Excel and Access.
Further Resources:
