Compare Excel Spreadsheets

Excel add-ins must first be converted to workbooks before using DiffEngineX to compare them. The steps to achieve this are described below.

1. Use File->Open to open the file in Excel.
2. Click the Developer tab and then the Visual Basic button. (Excel 2003: Tools->Macro->Visual Basic Editor.)
3. In the Project Explorer window in the Visual Basic Editor, you will see an entry similar to VBAProjectName(Add-in file name *.xla). Click the plus sign to the left of this. You may need to enter a password at this point.
4. Expand Microsoft Excel Objects and click ThisWorkbook.
5. From the Tools menu select VBAProjectProperties... .
6. Select the Protection tab. Uncheck "Lock project for viewing". Clear both the password and confirm password boxes.
7. In the Properties window change the value of IsAddin from True to False. (Make sure ThisWorkbook is selected in the Project Explorer.)
8. Select "Close and Return to Microsoft Excel" from the File menu.
9. For Excel 2007 and later select File->Save As and then select Excel Macro-Enabled Workbook (*.xlsm). For Excel 2003, select File->Save As and then Microsoft Office Excel Workbook (*.xls).

Once your Excel add-ins (*.xla) are converted to Excel workbooks (*.xlsm or for Excel 2003 *.xls), you can use DiffEngineX to compare them.

DiffEngineX is an executable (*.exe) file. It is usually run from its user-interface, but it can also be invoked with command line arguments. As such multiple Excel file comparisons can be automated in a *.bat or *.cmd file. Alternatively DiffEngineX can be simply called from the Windows Command Prompt.

The simplest invocation of DiffEngineX is given below.

"C:\Program Files\Florencesoft\DiffEngineX\DiffEngineX" /inbook1:"a.xlsx" /inbook2:"b.xlsx" /report:r.xlsx

If you are using Excel 2003 or earlier, please replace .xlsx with .xls.

Finding out how your document has changed from one version to another is an important topic, especially if it has modifications made to it by several different authors. This is especially the case for Microsoft Excel documents, which are used for financial planning, complex calculations and often contain Visual Basic for Applications macros. You may want to change one value between otherwise identical Microsoft Excel workbooks and see if there are any significant numeric differences. DiffEngineX is ideal for this as it can ignore differences below a user specified value or percentage change.

If you want to compare a plain text file, you have at your disposal a vast array of tools, including Microsoft WinDiff, which is included as part of Visual Studio. Microsoft Word has a built-in facility to compare an original with a revised document. However from Office '95 to Office 2010, Excel has no intrinsic functionality to find out how one spreadsheet differs from another one.

Excel documents are not straightforward to compare. The spreadsheet cells can either contain a formula (which performs a calculation based on the value of other cells) or a constant (text, number, date...). If it contains a formula, there is a choice of whether the formula itself should be compared or its calculated value (i.e. =6*7 or 42). Not only do the visible cells have to be compared, but also defined names, cell comments and the Visual Basic for Applications (VBA) macros.

Some spreadsheets contain data, Visual Basic macros and formulae. Other people use Excel to import and visualize imported database data. If using a difference algorithm to compare the latter, it is important to realize the data needs to be in sorted order first. (Excel's built-in sort functionality is under its Data tab.) Diff algorithms will not reorder rows to find similarities, but are limited to the insertion of blanks to line up the similarities.

Checking Use Alignment Plus on DiffEngineX's main user-interface changes from one row/column alignment algorithm to another.

When Align Rows is selected, blank rows are inserted into copies of your Excel spreadsheets in order to align similar rows. When the similarities are paired off with the same row numbers, the minimum number of differences can then be reported. If you have imported database rows in Excel, make sure you use Excel to sort the worksheets first (then Save them from the File menu) before using DiffEngineX to compare them. This is because DiffEngineX will not re-order rows in order to match them up.

What is the difference between the two different row alignment algorithms?