Suppose you have two Excel books or maybe two versions of the same book, which you want to compare. Or if you want to look for potential problems, such as manually entered (rather than calculated) totals or form breaks. If you have Microsoft Office Professional Plus 2013, you can use the Compare Microsoft Spreadsheet option to run a report on the differences and issues you encounter.
IMPORTANT: Comparison of worksheets is only available with Office Professional Plus 2013 or Office 365 ProPlus.
Comparison of the open spreadsheet
In Windows 7 On the Start menu, in Office 2013 Tools, click Compare Worksheet.
In Windows 8 On the Start screen, click Compare Worksheet. If you do not see a Compare Spreadsheet mosaic, start typing the words in the Comparison Spreadsheet, then select your mosaic.
In addition to comparing the spreadsheet, you will also find the companion program for access: Comparison of Microsoft Database. Office Professional Plus 2013 is also required. (For Windows 8, type Database Compare to find it.)
Compare two Excel books
Click Start> Compare Files.
The Compare Files dialog box appears.
Click the blue folder icon next to the compare box to examine the location of the previous version of the book. In addition to files stored on your computer or on a network, you can enter a web address to a site where books are saved.
File compare command
Click the green folder icon next to the box to find the location of the workbook that you want to compare with the previous version, and then click OK.
TIP: You can compare two files with the same name if they are saved in different folders.
In the left pane, choose the options you want to see in the results of the book comparison by turning on or off the options, such as Formulas, Macros Format Cell. Or, just select All.
Spreadsheet Compare Options
Click OK to run the comparison.
If you get “Can not open the book” message, this could mean one of the books is password protected. Click OK, and then type the password for the workbook. Learn more about how passwords work together and compare the spreadsheet.
The results of the comparison appear in a two-panel grid. The book on the left corresponds to the file (usually the oldest) “Compare” chosen and the book on the right corresponds to the “A” file (usually the most recent). Details appear in a panel below the two grids. Changes are highlighted in color, depending on the type of change.
Description of results
In the side-by-side grid, a worksheet for each file is compared to the worksheet in the other file. If there are multiple worksheets, they are available by clicking the forward and backward buttons on the horizontal scroll bar.
NOTE: Even if a spreadsheet is hidden, it still has it in comparison and is shown in the results.
Differences are highlighted with a cell fill color or text font color, depending on the type of difference. For example, cells with “written values” (non-formula cells) are formatted with a green fill color in the side-by-side grid and with a green font in the result pane list. The lower left panel is a legend that shows the meanings of the colors.
Results of comparing two versions of a book
In the example shown below, Q4 results in the previous non-late version. The most recent version of the book contains the final figures in column E of T4.
In the comparison results, the E2: E5 cells in both versions have a green fill which means that a written value has changed. Because these values change, the results calculated in the current year column also change: cells F2: F4 and E6: F6 have a blue-green fill that signifies the calculated value that was changed.
The result calculated in cell F5 also changes, but the most important reason is that in the previous version it was incorrect (sum only B5: D5, omitting the value of T4) its formula. When the workbook was updated, the F5 formula was corrected so that now has = SUM (B5: E5).
If the cells are too narrow to display the contents of the cell, click Resize Cells to Fit.
Change the size of the cells
Excel add-in consult
In addition to the comparative comparison functions of the spreadsheet, Excel 2013 has an Inquire add-in that you can enable to have a “Ask” tab. From the consult tab, analyze