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 visualizations and the functionality slightly differs between these.

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 "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 a Pivot table, on the type of the value used as the "Value" in the pivot chart.

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

Conditions for all types: These perform a check on the column value being NULL.

• Has value  
• Does not have value 

Conditions for Strings: These perform a check on a textual value in a case-sensitive manner.

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

Conditions for Numbers: These perform a numerical comparison.

• 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: These perform a check on a boolean value.

• Is true
• Is false

Date/Timestamp: These check against a given time range. The time range can be defined as relative, before, after or from-to, just like 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.) can be of three different kinds:

• Value: The value of a cell is compared against the entered value.
• Percentage: The value of a cell is compared to a percentage of values in the chart (column for Table chart or the measure for Pivot table). For example, "value < 50%" would match if the cell value is smaller than 50% of all values present.
• Percentile: The value of a cell is compared to the percentile of values in the chart. For instance, "< 50th percentile" means that the value is smaller than the 50th percentile of all values. In many scenarios, a percentile comparison provides more meaningful results, especially if the data contains outliers that could skew the arithmetic average. More details about percentiles.

Formatting

 There are 4 types of formatting you can apply when a condition matches:

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

2. Custom style: You can pick a custom color for text and background and apply Bold/Italics/Underline/Strike-through on the text.

3. Completely custom CSS: 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 an "Inline style": https://developer.mozilla.org/en-US/docs/Web/HTML/Element/style.

4. Icons: You can pick one of many icons to 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 the chart. If the formatting includes the background color, the background color from conditional formatting will take precedence over the color from the chart, allowing you to highlight some 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 for the formatting, you can express it 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 it from CSV/Excel) and use it 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.