Difference filtering in excel spreadsheets

Ok, so now let's look at difference filtering in excel spreadsheets using ComplyXL. One of the most powerful features of ComplyXL's graphical display is its ability to alter the criteria that identify two cells as different. Using this feature you can have the graphical display highlight only those differences that are of interest.

The following screen shot shows the majority of the options available to control the difference reporting. Additional options are available and will be shown when the mouse cursor is hovered over the More Filters list (circled in red).

Formula differences

Options in this group control whether differences within formulas should be highlighted. It maybe that differences due to formulas which result in date values are not important. In this case the Date option will be unchecked.

Cell value differences

Options in this group control whether differences to cell values (cells that do not have a formula) should be highlighted. It may be that only cells containing numbers are of interest in which case all options except Numeric would be unchecked.

General differences

Options in this group control whether other types of cell difference will be highlighted.

Formats
- Controls whether changes to formats will cause a cell to be highlighted
Types
- If the cell used to contain text and now contains a number this option controls whether or not the difference will be shown in the display.
In A not in B
- Controls whether a cell that is in worksheet A but not in worksheet B will be highlighted in the display
In B not in A
- Controls whether a cell that is not in worksheet A but is in worksheet B will be highlighted in the display


Hidden cells
- Controls whether hidden cells will be checked for differences
Blank cells
- Controls whether blank cells are ignore or not
Locked cells
- Controls whether cells checked for differences should include locked, unlocked or both sets of cells. This is useful in data entry applications where the worksheet has been setup to allow data entry into only certain areas. Changes to locked cells can be ignored and eliminated from the display allowing greater focus on the ones that a user was able to change.

Excel cell types

A cell value can be or a cell formula can return values of the following types:

Text/Label
- A set of characters such as "Hello"
Numeric
- A number
Date
- A cell formatted as a date or a formula such as =TODAY() that results in a date value
True/false
- A cell value of TRUE or FALSE or a formula that results in a true or false value
Error
- Normally a formula that is in error in some way such a one that divides by zero or includes a reference to a non-existent function

As you can see, this allows you to home in on the differences that are critial to you.