Even for the most organized book and record keepers, the sheer volume of information you store in Excel can soon become overwhelming. Microsoft Excel has two tools for comparing spreadsheet data, which can come in very handy if you're trying to find a cell typo or comparing documents to prepare for an audit. Both available only in Office Professional Plus 2013 and Office 365 Professional Plus, Spreadsheet Compare and Spreadsheet Inquire let you analyze workbooks or generate difference reports between two spreadsheets. The two can be huge time-savers over manual data comparisons.
Spreadsheet Compare
Step 1
Click on "Spreadsheet Compare" in the Start Screen of Windows 8. Type "Spreadsheet Compare" into the search field and select the file if you have trouble locating it on the screen.
Step 2
Click on the "Home" tab at the top and select Compare Files.
Step 3
Click on the blue folder icon next to the Compare field in the dialog box that appears. Browse through your saved Excel files and select the first spreadsheet you want to compare.
Step 4
Click on the green folder icon next to the To field on the next line. Browse through your Excel files to select the second spreadsheet you want to compare.
Step 5
Click on the "Ok" button.
Step 6
Choose the criteria you want to use in the comparison from the menu on the left. You can select criteria like "Cell Values," "Formulas" and "Cell Format," or just check the "Select All" box.
Step 7
Click on "Ok" to generate the difference report. Spreadsheet Compare creates a complete list of all differences between the two files based on the criteria you identified.
Spreadsheet Inquire
Step 1
Turn on the Inquire add-in if your haven't done so already. Click on the "File" tab in Excel and select "Options" and then "Add-Ins." Select COM Add-Ins from the Manage drop-down menu, click "Go" and make sure the box next to Inquire Add-Ins is selected.
Step 2
Click on the "Inquire" tab at the top of Excel to explore spreadsheet analysis and comparison options.
Step 3
Click on the "Compare Files" command to launch Spreadsheet Compare directly from Excel.
Step 4
Click on "Workbook Relationships" to generate a graphical report of references between your workbooks, such as formulae that reference a value contained in another spreadsheet. Use the Worksheet Relationships and Cell Relationships icons to do the same thing between worksheets in a workbook or cells in a worksheet.
Step 5
Click on the "Workbook Analysis" icon to generate a complete and interactive report on the open spreadsheet, including information about values, formatting, structure and formulae.
Tip
For version or network control, Spreadsheet Compare lets you compare two files with the same name, as long as they are saved in different folders.
An "Unable to Open Workbook" message in Spreadsheet Compare may indicate that a certain spreadsheet is password-protected. If that's the case, simply click on "Ok" and then enter the workbook password.
Spreadsheet Compare color-codes differences based on the type of data and the nature of the difference. The lower left pane of the difference report contains a legend indicating what each color means.
You can use Workbook Analysis in the Inquire options to complement comparison reports and better understand how making changes might affect the structure of your spreadsheet. Use the Cell, Worksheet and Workbook Relationships options to find out how making changes in one cell might affect all of your files.