Controlling the display colors in ComplyXL graphics - spreadsheet comparison

While in the graphical view you can change the colors used to highlight differences in the worksheet. To change colors click on the link in shown in the following two screen shots and which are circled in red.



Clicking on the link brings up the display properties editor. The editor shows two groups of properties: the colors for the main display and the colors for the scroll bar.

To change a color select the color you want to change and then select the color from the drop down that appears in right hand list (circled in red in the following screen shot).

Changes are displayed in the main window straight away so you can see which set of colors are the most appropriate for you.



Changes to any of the properties are saved when the Close button is pressed. The Reset properties button can be used to set all colors back to their default values.

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.

Zoom in/Zoom out of spreadsheet comparison (ComplyXL)

The display in ComplyXL can be zoomed to show more or less detail. The zoom factor is controlled by the zoom control.

The most extreme zoom out position is the Map view . In this view all rows of a workbook, no matter how many, are represented in the display.

Zooming in will display rows with increasing height. The most zoomed out position displays one row in one pixel. The next uses two pixel the next 4 pixels and so on.

Conversely, zooming out will decrease the number of pixels used to display each row to a minimum of one row per pixel.

Zooming in and out

There are four options to control zooming in and out.

1) Click on the level of zoom represented by one of the notches of the zoom control.
2) Click on Zoom In or Zoom Out to change the level of zoom by one notch
3) Use the '+' key to zoom in and the '-' key to zoom out by one notch

These three options will zoom in or out keeping the middle of the display centered if possible. it is not possible to maintain this objective when zooming out if, for example, the whole worksheet can be displayed. In this case the display will be shown with the first row at the top of the screen.

The fourth zoom option is to zoom in on a particular row. This is can be done by holding down the control key and clicking on the display at the point containing the row of interested. The display will be zoomed and the selected row centered in the display.

Centering limitations

The graphical display will try to center the row of interest in the zoomed display. Depending upon the zoom factor being used is may not be possible to center the row exactly because ComplyXL will always start the display at the beginning of a cell. This doesn't make any perceptible difference when the display uses one or two pixels to display each row. However at higher magnifications, the slightly off-centered positioning can be noticed.

Understanding the scrollbar in ComplyXL for viewing and comparing spreadsheet changes

I thought I'd try to give as much detailed information about how ComplyXL works. I've already shown the graphical views within ComplyXL and how detailed a report you can get with a couple of clicks. Today, I thought I'd focus on the scroll.

The scroll does more than allow the display to be scrolled up and down in ComplyXL spreadsheet comparison.

Background map

The background to the scroll bar is a map of the rows of the worksheets. Each row containing one or more differences is represented by an orange line in the scroll bar background.

If rows contain cells but there are no errors the row will be represented by a pale green color. If rows do not contain values they will be represented by a white line.

As with all scroll bars, the background can be clicked on to scroll the display window. A click above the thumb will move scroll up while a click below the thumb will move it down. Each click will move the scroll bar display up or down by one page.

Thumb

The scroll bar thumb (represented by the light blue area) indicates the relative position of the current display in the whole workbook. The size of the thumb indicates how much of the workbook is represented in the current display.

The thumb is also translucent so that the background can be seen through the thumb at all times.

Scroll buttons

The scroll buttons at the top and bottom of the scroll bar scroll the display up or down by one cell.

The map presented in the background is useful as it allows you to see where differences exist relative to the scroll bar thumb and where to scroll to review the differences.

Keyboard navigation

The scrolling behavior of the display can be controlled from the keyboard. The up and down arrow keys will scroll the display by one cell. The page up and page down keys will scroll the display by one page.

Just wanted to show a sample screenshot of what can be displayed using ComplyXL.
One of the most powerful features of the 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.

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