Conditional

functional

Color cells by their values for one query: highlight rules, color scales, and data bars.

The conditional directive colors cells by their values, for this query only: red text when a status says Failed, a pink fill when a balance goes negative, a green-to-red color scale across a latency column, or a data bar behind each revenue figure. Write one rule per line, as many lines per column as you like.

Syntax

-- sqlly conditional: column=<name>; <effect>[; <style>…]

A rule applies to every result column with that name, matched without regard to case. The effect is one of:

Effect	Meaning
`when=null`	The cell is `NULL`.
`when=not null`	The cell is not `NULL`.
`when=> N`	A number greater than N.
`when=< N`	A number less than N.
`when=between A and B`	A number from A to B, both included. The bounds can be in either order.
`when=is TEXT`	Text equal to TEXT, ignoring case.
`when=contains TEXT`	Text containing TEXT, ignoring case.
`scale=#RRGGBB..#RRGGBB`	A color scale: the background blends from the first color at the column's lowest value to the second at its highest. Numeric columns.
`bar=#RRGGBB`	A data bar: a bar as long as the value, drawn behind the text. Numeric columns.

A when= rule needs at least one style:

Style	Meaning
`text=#RRGGBB`	Text color.
`fill=#RRGGBB`	Background color.
`bold`	Bold text.

Colors are written #RRGGBB, or #RRGGBBAA with an alpha channel for a see-through fill. The text after when= can't contain a ;.

Examples

-- sqlly conditional: column=Status; when=is Failed; text=#B00020; bold
-- sqlly conditional: column=Balance; when=< 0; fill=#FDE7E9
-- sqlly conditional: column=Latency; scale=#63BE7B..#F8696B
-- sqlly conditional: column=Revenue; bar=#5B9BD5
-- sqlly conditional: column=ClosedAt; when=null; fill=#FFF4CE80
SELECT Status, Balance, Latency, Revenue, ClosedAt FROM accounts;

Several rules for one column read top to bottom. Here a large total is bold green, a negative one is red, and every value also gets a data bar:

-- sqlly conditional: column=Total; when=> 10000; text=#1E7B34; bold
-- sqlly conditional: column=Total; when=< 0; text=#B00020
-- sqlly conditional: column=Total; bar=#5B9BD5

Which rule wins

The first matching when= rule for a column styles the cell; later matching rules are skipped.
A matching rule's fill beats a color scale, wherever the scale's line sits. Text color and bold from the rule still combine with the scale and with a data bar.
One color scale and one data bar per column: when there are several, the first of each applies.
Directive rules come before the rules saved in the Conditional Formatting dialog, so a query can override your everyday colors for its own columns.

Number conditions, color scales, and data bars only look at cells the database returned as numbers; numeric-looking text is left alone. A column whose values are all the same paints the middle color of its scale and full-length bars.

The rules travel into an Excel export as native Excel conditional formatting, in the same order, so the workbook is colored like the grid and keeps up when values change there.

Let the lock write it. Set up rules in the Conditional Formatting dialog, then click the lock in front of the result's name: SQLly writes a conditional: line for every rule that applies to the result's columns. See Lock the grid.