Conditional Formatting

Conditional formatting allows to apply formatting (colors, styles) or add icons based on a set of conditions. It is available for Table, Pivot table and Number chart and the functionality slightly differs between these 3 chart types.

Rules

You can define one or more rules, each rule defines a condition and corresponding formatting. When rendering a table cell or a value, the rules are evaluated from top to bottom, and when the rule condition is matched, the corresponding format is applied.

When the "Stop when applied" is checked and the condition matches, the subsequent rules are not evaluated for that particular value.

Conditions

The set of conditions available depends on the data type of the selected column values, or, in case of Pivot table, on the type of the value used as"Value" in the pivot chart.

For Table and Number chart, you can select any of the columns in data for the condition evaluation (including hidden columns, see below in the Special cases section). For the Pivot table, you can base the condition on the values used as Value, Row or Column header.

Common to all types: conditions match based on the cell value being NULL.

• Has value  
• Does not have value 

String: check the textual value in a case sensitive manner against the entered value.

• Starts with
• Ends with
• Contains
• Does not contain
• Is exactly
• Is not exactly

Number: perform the numerical comparison against the entered value(s)

• Is equal
• Is not equal
• Is less than
• Is less or equal than
• Is greater than
• Is greater or equal than
• Is between
• Is not between

Boolean: logical check of the value

• Is true
• Is false

Date/Timestamp: check against the entered time range - all definitions of a time range are supported - relative, before, after or from-to, as you are used to in the timerange filter.

• Is in timerange
• Is not in timerange

Numerical comparisons

For Table chart and Pivot table charts, the numerical comparisons (=, >, <, between, etc.) support 3 types of value comparisons:

• Value - value of a cell is compared against the entered value
• Percents - value of a cell is compared to the percentage of the range of values in the chart (column for Table chart or the measure for Pivot table) - e.g value < 50% would match if the cell value is smaller than 50% of the values present
• Percentile - value of a cell is compared to the percentile of the range of values present. For instance < 50th percentile means that the value is smaller than 50% of the values present. In many scenarios, a percentile comparison provides more meaningful results, esp. if the data contains outliers that could skew the arithmetic average. See here for more details about percentiles.

Formatting

 There are 4 types of formatting you can apply:

1. Presets: Red/Yellow/Green color on Red/Yellow/Green background, Red/Yellow/Green background, Red text. The presets are tuned to work well in both light and dark mode.

2. Custom style, where you can pick a custom color for text and background and apply Bold/Italics/Underline/Strike-through for text.

3. Completely custom CSS string giving you the full power of CSS to alter the formatting of the values. The value would be what you would use in HTML as "Inline style": https://developer.mozilla.org/en-US/docs/Web/HTML/Element/style

4. Icons: You can pick one of many icons that would be placed in front of the text in the cell.

Special case: Pivot chart with data-driven colors

In case you add formatting rules for a Pivot chart, that already uses data-driven colors, the formatting will expand the formatting in addition to the background color from data. If the formatting includes the background color, the background color from conditional formatting will take precedence over the color from data, allowing you to highlight some specific values, while still keeping the gradient background for the other values.

Special case: Complex conditions

For Table and Number charts, if you need a complex condition to decide the formatting, you can express this in SQL as an extra column that would produce values used only as a condition for the formatting. You would hide this column (and perhaps also exclude from CSV/Excel) and use only in the conditional formatting rules.

When a hidden column is chosen in a rule, you can select any other column to which the formatting should be applied.