The Fool on the Hill

Spreadsheet Compare is a Microsoft Excel Add-In, written in VBA, that performs a cell-by-cell comparison of Worksheets within the same or different Workbooks. Requires Excel 2000 or later.

The Spreadsheet Compare project is hosted on SourceForge:

• Project Summary Page (project forum, bug reports, feature requests, stastics, etc.)
• Download Spreadsheet Compare

Features

• Compare Excel Worksheets/Excel Workbooks.
• Performs a cell by cell comparison of two workbooks.
• Multiple Worksheet can be checked at one time.
• Toolbar and menu access from within Excel.
• Generate a simple report Workbook of the compare results.
• Generate a merge differences report of the compare results.

Example:

Lets say that you are asked to compare some expenses from last year against this years. The two spreadsheets are shown below. What a pain to compare the numbers in each to see the differences.

Spreadsheet Compare performs a cell-by-cell check of the two files and highlights the differences and adds a column to show you which columns have been changed. The screen shot below shows the result. Note that both the actual and forecast spreadsheets have been highlighted to make it easy to see the changes.

Automatically generate a comparison report as shown below.

Automatically generate a comparison report as shown below.

Step by Step Instructions:

1. Open the Workbook or Workbooks to be compared.
2. Start Spreadsheet Compare.
3. On the 'Select two spreadsheets to be compared' form, select the Workbook(s) to be compared. To compare two worksheets within the same workbook, select that workbook in both drop down list. Click next.
4. On the 'Processing Options' form, select the processing options that you want:
   1. Start Row - Starts the compare from a particular row (useful when running long compares that have failed because of a mis-match).
   2. Delete Change Column - Self explanatory.
   3. Clear Existing Sheet Colours - Removes any cell colouring from the worksheet.
   4. Case Sensitive Comparison - self explanatory. If this is unchecked, the cell values read are converted to strings and the changed to upper case and compared. Should be checked by default.
   5. Add Change Column to show - Adds a column to the spreadsheet to indicate changes.
      1. Mismatched Column Name - Useful if the first row is a column name (for DB comparisons).
      2. Count of Mismatched Cells - Self explanatory.
      3. Eye-catchers - Self explanatory.
   6. Highlight changes with: - Set a colour to highlight the changes.
5. On the 'Select Worksheets from First Workbook' form, if the workbook contains worksheets that you do not want to compare, select them and click the remove button. If you are comparing worksheets within the same workbook, remove one of the two worksheets that you want to compare. Click next.
6. On the 'Select Worksheets from Second Workbook' form, if the workbook contains worksheets that you do not want to compare, select them and click the remove button. If you are comparing worksheets within the same workbook, remove the worksheet kept in the previous form. Click next.
7. On the 'Final Options' form, check any final options:
   1. Stop on miscompare - stops on any miscompare.
   2. Stop on miscompare in the first column only - self explanatory.
   3. Generate Report - self explanatory.
8.  Click 'Start Compare'