The Short Guide To DiffEngineX - Compare Excel Worksheets

Guide To Compare Excel Workbooks Spreadsheets With Software

DiffEngineX is an implementation of the classic "diff" algorithm as applied to comparing Excel spreadsheets. This means that when you turn on its Align Rows functionality it will insert blank rows in order to get similar rows to line up in automatically made copies of your spreadsheets. It does not reorder rows. When aligning rows DiffEngineX does not consider the whole row, only the columns you specify as important when determining which rows in spreadsheet #1 are similar to the rows in spreadsheet #2.

After the optional row alignment, DiffEngineX performs a cell by cell comparison of your spreadsheets. Hide Matching Rows found on the Extras dialog allows you to quickly see all the differences at once on the two color highlighted sheets.


I am comparing rows of data / lists of members / lists of inventory.
What does this mean for me?

Comparing Excel Imported Rows Databases

DiffEngineX can compare rows of data or lists perfectly well, but with one caveat. This results from the fact that it will insert blank rows to align similar rows, but will not reorder rows. The rows of data need to be pre-sorted first. Excel can do this for you via its Sort functionality under its Data tab or menu. After sorting in Excel make sure you re-save the changes. If you need to sort on a combination of columns, you need to hit Excel's Add Level button.

If your data is already in sorted order you can miss out this step. Make sure you turn on DiffEngineX's Align Rows and Use Alignment Plus functionality. Typically Align Columns can be left turned off.

When dealing with large numbers of rows, you may want to turn on Hide Matching Rows found on DiffEngineX's Extras dialog.

For more information, please refer to our tutorial on comparing lists.


I am comparing formulae based spreadsheets.
What does this mean for me?

Excel Formulae Forumlas Differences

Obviously it means you can forget about the need to pre-sort your worksheets. However if new rows have been inserted in the middle or at the top of your spreadsheets, you will need to turn on Align Rows. Make sure Use Alignment Plus is turned off. Typically we have found in formulae based spreadsheets that Use Alignment Plus inserts too many blank rows - it is only intended to be used with data based spreadsheets.


What are the reports that DiffEngineX produces called?

Excel Difference Reports

Typically DiffEngineX will generate three new workbooks per comparison. The first two will be color highlighted copies of your spreadsheets and will have the same names as the original ones, except for a number appended on the end. The third one will be the difference report and will typically be called Sheet1. Each sheet in the difference report lists the differences found on the worksheet of the same name in your original spreadsheets.


How can I see differences reported at the character level?

Excel Cell Text Formula Character Level Differences

If your spreadsheets have a large number of differences, highlighting differences at the character level will greatly slow down comparisons.

Checking Highlight Character Level Differences on the main part of DiffEngineX's user interface will show differences at the character level directly on automatically made copies of your worksheets.

Checking Color in red precisely the parts of formulae and text constants that differ on the Options dialog will show character level differences on the difference report.


Is there anything else?

Excel Difference Report Hyperlinked Hyperlinks To Compared Spreadsheets

Turning on Add Hyperlinks to aid navigation via the options dialog, links each reported difference to both the color highlighted copies and your original spreadsheets (Excel 2007 and above only).



Compare Excel Workbooks Worksheets With DiffEngineXViewing the two color highlighted sheets side-by-side with synchronous scrolling is often an easier way to see the differences than by inspection of the difference report alone. Click here for details on how to do this.


Click here for a tip on how to make viewing the results much easier.

tip #2

If you see an error message stating Microsoft Excel may not be installed, you may need to repair your installation of Microsoft Office. For more details click here.

tip #3

In order to just see the new (green) or deleted (red) rows in both color highlighted worksheets, you can make use of Excel's built-in Filter capability. Just create a filter and then select a column to filter on Cell Color.

tip #4

If comparing rows of data, you may need Excel's Data tab to pre-sort. Then re-save before invoking DiffEngineX.

tip #5

If your region, language, location, date, time or number format settings have been altered or do not match the language version of Excel, you may need to turn on the DiffEngineX Internationalization option to stop errors.

tip #6

If you turn on Add Hyperlinks to aid navigation, the links work best if the workbooks being compared are initially closed (only applies to Excel 2003). For more details click here.