Compare Worksheets (Auditor)
The Compare Worksheets tool is an auditing tool that highlights the differences between one worksheet and another. The differences are highlighted in the second sheet selected, which is treated as the audit sheet. The difference highlighting is prioritized by formulas, values, number and date formats, and then cell formats.
Compare Worksheets Form Fields
- Worksheet 1: Select the workbook and worksheet names that the other sheet will be compared to.
- Worksheet 2: Select the workbook and worksheet that will be audited. This is the sheet that will be color coded to highlight the differences.
- Comparison options: Check the boxes for the types of information to compare.
- Color code: Optional, change the color code for each type of cell difference.
When you click OK, the differences between the sheets are analyzed, the audit sheet is color coded, the two workbooks are displayed side by side, and the Compare Navigator dialog opens on the right-hand side of Excel.
Compare Navigator Form Fields
- Change List: Lists the cell differences, including the cell address and the type of change formula, value, number / date format, or cell format.
- Baseline and Audit cell values / formulas: These two read-only fields show the baseline and audit cell formula for the item selected in the Change List. No values are displayed for number or cell format differences.
- Copy: You can choose to copy all the cells or just the selected cells from sheet 1 to sheet 2 or sheet 2 to sheet 1. Note however, that since sheet 2 has had the auditing formatting applied to cell differences, copying from sheet 2 to sheet 1 will copy the formulas, values, and number formats, but styles are not copied.
- Restore formats and view upon closing: Restores the full worksheet view from side by side, and automatically applies Restore Format. If you uncheck this option, you can manually reset the view by clicking the Excel View tab and un-toggling the "View Side by Side" and "Synchronous Scrolling" options, and then clicking the Restore Format button on the Spreadspeed ribbon tab.
Comparing Worksheets in the Same Workbook
The Compare Worksheets tool uses the Excel view side by side option, which only works with separate workbooks. If you want to compare two sheets in the same workbook, you can make a copy of one worksheet to use as the baseline sheet by following these steps:
- Right-click on the baseline worksheet tab.
- Select "Move or Copy ..." from the context menu.
- On the Move or Copy dialog, under "To book:" select "(new book)", and then check the "Create a copy" option.
- When you click OK, a copy of the sheet is created in a new workbook, which you can use for the Compare Worksheets baseline.