Conditional Formatting of Numerical values in a Column
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.