Conditional Formatting of Numerical values in a Column

This menu allows numbers within a spreadsheet column to be coloured according to a set of conditional rules. Up to three rules can be applied to a column, although the between rule counts as two. The rules are applied sequentially, so that if the first rule applies, that is the colour used. Thus the ordering of rules can be important. For example the rules "Greater than 10", "Greater than 20" and "Greater than 30" in that order would not give the required three colours, as all values greater than 20 or 30 meet the first condition and so would be just coloured in the first specified colour. To get three colours the rules would need to be in the order of the most restrictive to the least, i.e. "Greater than 30", "Greater than 20" and "Greater than 10". The conditional formatting details are saved in the GSH file and will be displayed when the spreadsheet is reopened.

The Background shading options allow you to set coloured backgrounds for the cells in each column, based on the where the cell values falls between the specified shading minimum and maximum values. The colour saturation value controls the intensity of colour used, 100% being maximum intensity, and low % values giving lighter pastel colours.

Column

This lists columns in the currently selected spreadsheet. Selecting a column from this drop down list will apply the specified conditional format to be applied to this column when the OK or Apply button is clicked.

If columns are currently selected in the spreadsheet you can use the All Selected Columns list item to apply the conditional formatting to all the selected columns. The option All Numerical Columns can be selected to apply the conditional formatting to all the numerical columns within a spreadsheet.

Comparison

The type of comparison to be made with the specified value. Options for this include:

Blank	No comparison.
Equals (==)	The column value is equal to the specified value.
Less than (<)	The column value is less than the specified value.
Greater than (>)	The column value is greater than the specified value.
Less than or equals (<=)	The column value is less than or equal to the specified value.
Greater than or equals (>=)	The column value is greater than or equal to the specified value.
Between and	This item requires two specified values and is true when the column value falls between these two values.

With value

This is the value that the column items are compared with. If the comparison is true, then the column value will be displayed in the specified colour.

Using colour

Lists the colours that can be used to display text when the conditional statement is true. You can choose between Red, Blue, Green, Cyan, Magenta, Yellow, Dark Red, Dark Blue, Dark Green, Orange, Brown, Pink, Dark Gray, Gray, Light Gray and Black. The default colours and fonts for column values in the spreadsheet can be customized using the Options - Fonts and Colours menu.

Background Shading

The Background shading options allow you to set coloured backgrounds for the cells in each column, based on the where the cell values falls between the specified shading minimum and maximum values.
The drop down list provides a range of colour series interpolated between the shading minimum and maximum values. The maps available are:

Blue-Red - Blue to White to Red.
Heat - Blue to Green to Red to Yellow to White.
Topographical - Blue to Green to Brown.
Rainbow - Red to Orange to Yellow to Green to Blue to Indigo to Violet.
Outlier - Values equal to or below the shading minimum are coloured Blue, and those equal to or above the maximum are shaded Red.

Shading Minimum

The minimum value used in the colour shading. Values close to, or below the minimum value will be colour given the first colour in the colour map.

Shading Maximum

The maximum value used in the colour shading. Values close to, or above the maximum value will be colour given the last colour in the colour map.

Reverse Colour Map

If this item is selected, the colour list will be reversed. For example the Blue-Red map will go from Red for the minimum to Blue for the maximum.

Colour Saturation

The colour saturation value controls the intensity of colour used. A value of 100% gives the maximum intensity with bright colours, and lower percentages giving lighter pastel colours.

Suspend conditional formatting on this spreadsheet

When this is checked, the conditional formatting is suspended on all columns. This provides an easy method of turning off the formatting temporarily, whilst retaining the ability to re-enable it at a later date.

OK

Apply the specified conditional formatting on the selected column and close the dialog.

Apply

Apply the specified conditional formatting on the selected column and keep the dialog open so another column can be formatted.

All

Apply the specified conditional formatting to all columns in the spreadsheet and close the dialog.

Clear

Clear any conditional formatting on the selected column.

Cancel

Close the dialog without making any changes.