Data Cell Colour Rules

You can improve the visualisation of data within your table by applying different cell shading or Text formatting based on the value contained. For example, cells with values above 50 could be shaded red and cells with values below 50 in green. This style of data presentation has been referred to as “tartan rug”. It can be achieved using the following settings for the Data Table widget or Time Series Table widget:

Data Cell Colour Rules – a comma-delimited string where each delimited value has the form <value>|<rule>|<style>.

<rule> is one of:

  • eq (equal)
  • neq (not equal)
  • gt (greater than)
  • gte (greater than or equal to)
  • lt (less than)
  • lte (less than or equal to)

<style> is of form:

<colour>;<font-weight>;<font-style>;<font-size>.

This is used in conjunction with:

Cell Rule Behaviour – Text or Background. If Text, then the above colour rules will apply to text. If Background, the background of the cell will be shaded instead.

For example, you might want cells shaded red where the value is above 50. This can be achieved by giving Data Cell Colour Rules a value of ‘50|gt|#ff0000’. Note that there are many web pages that list of colour codes, such as http://www.w3schools.com/cssref/css_colorsfull.asp.

colour rule table settings

The resulting table looks like this:

table with red cell

Some further examples for Cell Rule Behaviour set to ‘Background’:

A value of ’50|gt|#ff0000,50|lte|#00ff00′ causes cells with values above 50 to be shaded red and those with values equal to or less than 50 to be shaded green.

table with red and green cells

A value of ‘ ‘50|gt|#ff0000,20|lte|#00ff00,50|lte|#ffbf00’ causes cells with values above 50 to be shaded red, cells between 50 and 20 to be shaded amber and those with values equal to or less than 20 to be shaded green.

table with red, amber and green cells

Please note that the order of the rules is important. The string is read from left to right and as soon as the table widget finds a rule that is true, it applies the respective style and then ignores all further rules. So giving the Data Cell Colour Rules a value of ‘50|gt|#ff0000,50|lte|#ffbf00,20|lte|#00ff00’ will not shade any cells green.

Some examples for Cell Rule Behaviour set to ‘Text’:

A value of ’50|gt|#ff0000,50|lte|#00ff00′ causes the text with values above 50 to appear in red and those with values equal to or less than 50 to appear green.

table with red and green text

A value of ’50|gt|#ff0000;bold;italic;18px,50|lte|#00ff00;bold’ causes the text with values above 50 to appear red, bold, italic and with a font size of 18px. Text with values equal to or less than 50 to appear green and bold.

table with red and green formatted text

Substituting text values on the basis of colour rules:

It is also possible to substitute text values on the basis of colour rules as well as making use of the styling functionality. You could adapt the same code used to display the previous table but amend it by using a further | character after the style rules:

Data Cell Colour Rules set to ’50|gt|#ff0000;bold;italic;18px|High,50|lte|#00ff00;bold|Low’ with Cell Rule Behaviour set to ‘Background’ produces a table like this:

table with substituted text