Conditional formatting

Conditional formatting allows us to change properties of table cells or text box tags that meet a certain condition.

Tables

There are two formatting options for tables:

regular (binary) formatting - colors cells a certain color when a condition is met;
gradient formatting - paints cells in different shades; the saturation depends on how close the corresponding value is to the target.

Let's have a look at the regular one first.

Regular formatting

Consider the following table:

	Column 1	Column 2	Column 3	Column 4
Row 1	20	30	45	25
Row 2	55	75	10	15
Row 3	30	40	60	80

Let's say we need to paint the background of all cells, whose values are greater than 50, green. Then the conditional formatting rule would be this:

CONFORMAT((2:4, 2:5) > 50, bg_color="green")

For table shapes, we must specify the group of cells the formatting rule should be applied to. In our example, we are targeting all cells containing numerical values: (2:4, 2:5). It reads as "all cells from row 2 to row 4, from column 2 to column 5". We could also write it as (2:, 2:), omitting the end row and the end column. It reads as "from row 2 to the last, from column 2 to the last". If we needed to target only the 2nd column, we'd write it like this: (2:, 2).

The formatted table would look like this:

	Column 1	Column 2	Column 3	Column 4
Row 1	20	30	45	25
Row 2	55	75	10	15
Row 3	30	40	60	80

It's possible to declare multiple rules and write more complex conditions, using loglcal OR (denoted as |) and AND (&):

CONFORMAT(
    (2:, 2:) < 100 & (2:, 2:) > 50,
    font_color="#ff0000"
)

CONFORMAT(
    (2:, 2:) <= 0 | (4, 2:) <= 50,
    bg_color="green" 
)

Gradient formatting

To declare a gradient formatting rule we use the HEATMAP function name. The function must have 4 arguments:

rng - a range of cells that should be formatted;
min - a function that determines the min value. Available options: MIN.
mid - a function that determines the mid value. Available options: PERCENT, PERCENTILE;
max - a function that determines the max value. Available options: MAX.

Let's say we want to color background of cells in the 2nd and 3rd rows in a 3x3 table. The min value (in the specified area) should be colored red; the average value - white; the max value - green. Here is how we would do it:

HEATMAP(
    (2:, 1:),
    min=MIN(bg_color="red"),
    mid=PERCENT(50, bg_color="white"),
    max=MAX(bg_color="green")
)

The result could look like this:

45	11	100
22	50	82
12	33	62

Here is another example with PERCENTILE:

HEATMAP(
    (1:, 1:),
    min=MIN(bg_color="red"),
    mid=PERCENT(25, bg_color="white"),
    max=MAX(bg_color="green")
)

The result could look like this:

2	4	8
11	12	13
39	40	41
60	61	62

Text boxes

Consider the following example:

Let's say we have a text box with this content: base values: {{base1}} {{base2}} {{base3}} and the tag values are base1=90, base2=180, base3=150. We need to use red font color for all tag values below 100. Then the formatting could be this:

CONFORMAT({"base1", "base2", "base3"} < 100, font_color="red")

Note that we refer to tags differently than we refer to table cells: we simply list the tags using a comma as a separator and enclosing them in curly braces.