The diff tool lets you find differences between Excel files, in cells as well as charts, comments, VBA macros and so on. To accomplish that goal, it offers a simple way to produce color-coded Excel spreadsheets and navigation links.
The color coding works as follows :
Note : the diff tool leaves Excel spreadsheets intact. What it does is create a new consolidated color-coded spreadsheet.
Also the color-coding can be changed in order to make it more suitable for color-blind users.
Navigation links are added to changes and deletions so you can click on cells and navigate back and forth to the corresponding cells in the older Excel file, and back to the consolidated diff. This is an intuitive mechanism to help you understand what changed.
The diff tool compares value and formula differences in cells, as well as charts data and titles, text boxes, vector shapes, comments and VBA macros. Future evolutions of the product may include comparing other elements. Feel free to tell ARsT Design what you would be interested in.
Click to download and install the trial version. Double-click on the .msi file in order to start the install process. It is possible that on your operating system for the install to work properly, you must instead right-click on the .msi file and choose Run as administrator.
If you are getting the "Windows protected your PC : Windows SmartScreen prevented an unrecognized app from starting" message with a blue background on Windows 8, 8.1, 10, then click on More Info and then on Run anyway. The reason why this message appears is that Microsoft blocks applications by default for no other reason that they are new or have been recently updated.
How to use it
As part of the install process, the diff tool materializes as a shortcut in a new program group in the Windows start menu titled "xlsgen diff tool".
Computing differences between Excel files, also known as diffing Excel files, works by comparing each cell in each worksheet of a pair of Excel spreadsheets (as well as other objects such as charts). A difference is measured up whenever the cell content differs, whether it is an addition (the cell content did not exist before), a change (the cell changed), or a deletion (the content has been blanked out).
For a comparison to make sense, you need to provide a pair of Excel files. Of course, if the pair of Excel files are revisions of an Excel file over time, it makes even more sense. Each pair of files has one of them called the oldest, and the other called the newest. As can be guessed, by oldest and newest is meant two Excel files where the second, or newest, is an update of the oldest. The tool also allows to pass not single files but entire folders, and use wildcards to limit which files will be impacted in a folder.
To show the color-coded Excel workbook for a diff, you need to right-click a result, and select open diff workbook in Excel. Note you can select multiple results at once.
Important features of the diff tool is not only to compute a difference metric between Excel file pairs. Aside providing a way to know how many differences there are between a pair of Excel files, the diff tool lets you automatically create a color-coded Excel spreadsheet visually representing the differences so you don't have to find them yourself. Here is the meaning of the colors (standard color theme used in diff tools) :
The color-coded spreadsheet carries the cell values found in the newest spreadsheet combined with colors. It is used to explain what are the changes. Each change or deletion is created with an hyperlink. When you hover the mouse on top of such orange or red cell, the cursor changes to a hand shape and you can click it in order to see the oldest value of the cell. This is achieved by opening the oldest Excel file and making sure the cursor is set to the same position. To switch between spreadsheets, you can use Ctrl-Tab on the keyboard, or Excel's windows switching from the main menu.
You will also notice that worksheet tabs themselves are color-coded. In the example above, the first worksheet is orange in order to show that at least one the its cells have changed. If your Excel files are made of many worksheets, this worksheet tab coloring is useful to find out where are the changes, without having to wade through the cells first. As you can see, the color-coding is entirely geared towards productivity and ease of use.
But there is more. The tool can also generate an xml export that keeps old and new cell values side-by-side in a square matrix, letting any developer with an xml parser at hand find out where the differences are. Creating this xml export is done by right-clicking the results and choosing the appropriate option. This creates an xml file in the working folder and then opens it in the default web browser.
The diff tool also analyzes VBA macros anywhere they are in the spreadsheets, that is at the workbook level, in modules, or in worksheets. Below is an example of color-coded changes between two versions of VBA macros, as computed by the tool :
Defining the Excel files or folders is as simple as it can be thanks to the File and Folder buttons on the right. When you choose a folder, the diff tool will take into account all files with the .xls files in them, including all of its subfolders. In addition to defining a file or a folder you can edit the filepath or folder and add one or more wildcard characters. Wildcards are characters ? and * and can be used to identify one or more Excel files. ? represents an arbitrary character, while * represents one or more arbitrary characters. For instance you would type SalesReport*.xls to identify whatever Excel file whose name begins with SalesReport. In this regard, files such as SalesReport.xls, SalesReport01.xls, SalesReportDecember.xls are all valid. Those options should be flexible enough to accomodate your needs whether you are comparing Excel files that are in the same folder, or in separate folders, or have a name which follows a particular policy.
Clicking the Options button brings the Options dialog.
Ignore worksheet names makes it possible to tell the diff tool if the list of sheet tabs in the spreadsheet is taken by order or by name.
Check formulas tells the diff tool to take a look at formulas instead of values.
Strict value match tells the diff tool to compare values strictly, as opposed to ignoring small differences such as spaces and punctuations.
With regards to excluded ranges, those are typical Excel cell areas. For instance A1:B4. Note that you can exclude more than one area by inserting a semi-colon (;) between cell areas. Excluded ranges are ignored in the diff process. What are they good for? You may be aware of the fact that in some known place in the Excel file, some value keeps changing, for instance because it's formula with a dynamic date, and thus no matter what the diff process will always return a difference. Those differences, while valid ones, may be of less interest than other diffs, which is why the excluded range(s) option is useful. If you don't want to exclude ranges, then leave that field alone.
Excel passwords is a list of passwords that the diff tool uses in order to open password-protected Excel files.
Color options are the opportunity for color-blind users to change any of the three colors used when a diff is computed, i.e. additions (green by default), deletions (red by default) and changes (orange by default).
A good thing to know about all those options is that they are persisted when the diff tool is quit. If you are using this tool often, you will probably appreciate not to have to enter the same information or select the same files over and over again.
Purchase a license
Because the trial version is such that the tool will only do a fraction of what it is supposed to do, you probably want to purchase a license.
The license is an end-user license. It does not include right to redistribution, leasing, ownership transfer in any form or fashion.
The xlsgen diff tool is a product from ARsT Design, an independent software developer. ARsT Design can be contacted at the above email address.
Copyright ARsT Design 2017, all rights reserved.
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.