Conditional formatting

Color cells by their values with highlight rules, color scales, and data bars, saved for a column name or written into one query.

Conditional formatting colors cells by what is in them, so the values that matter jump out of a long result: failed jobs in red, negative balances on a pink fill, slow queries shading from green to red, revenue drawn as bars. It never changes the data - copy and export still carry the database's own values.

There are two ways to set it up, and they work together:

• The Conditional Formatting dialog saves rules for a column name. They apply in every result grid with a column of that name, in every query.
• The conditional: directive writes rules into one query. They travel with the file and apply only there.

The Conditional Formatting dialog

Right-click a result column header and choose Format › Conditional Formatting…. The dialog is titled with the column's name and edits that column's rules.

SQLly

• The rule list at the top shows the column's rules in the order they are checked, each in a short sentence such as When value > 10000 → background + bold, Color scale over the column's range, or Data bar proportional to the value. Remove deletes a rule. A column without rules says No rules for this column yet.
• Rule picks the kind of rule to add (all listed below). Rules that compare against a value show a number or text box; between shows two.
• Text, Background, and Bold set the style of a value rule. Pick at least one.
• Low and High set a color scale's two colors (green to red to start with), and Bar color sets a data bar's color.
• Add Rule adds the rule to the end of the list. If something is missing, the dialog says what next to the button: Enter the text to match, Pick a text color, background, or bold for the rule, or that a value is not a number.

Changes apply as you make them: the grid behind the dialog repaints right away, and other result grids pick the rules up when they next refresh. The dialog doesn't block the grid, so you can keep scrolling while it is open; Esc or a click outside closes it, and the pop-out button moves it into its own window. There is no reordering: to move a rule up, remove the rules above it and add them again.

Every kind of rule

Number rules, color scales, and data bars only look at cells the database returned as numbers; a number stored as text is left alone, and text rules only look at text cells. The lowest and highest values come from the rows loaded in the grid. When every value in the column is the same, a color scale paints its middle color and data bars are full.

Which rule wins

1. Directive rules come first, then the rules saved in the dialog, each in its own order.
2. The first value rule that matches styles the cell. Later matching value rules are skipped, so put the most specific rule first.
3. A matching rule's background beats a color scale. Its text color and bold still combine with the scale's background and with a data bar.
4. One color scale and one data bar per column: if there are several, the first of each applies.

Rules match column names without regard to case, and a rule applies to every column of that name in the result.

Saved rules or directives

From the dialog into the query

Set the rules up in the dialog, where you can see them as you go, 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 into the query's lock block, along with the rest of the grid's setup. Anyone who runs the file then sees the same colors, whatever rules they have saved themselves.

In Excel exports

An Excel export or Open in Spreadsheet carries the rules into the workbook as Excel's own conditional formatting: highlight rules on the cell value, blanks and text-contains rules, two-color scales, and data bars, with the same colors and bold. The query's conditional: rules come first, then your saved rules, and the first matching highlight rule still wins, so the workbook looks like the grid and keeps coloring correctly when someone changes a number.

Writing directives

One rule per line, as many lines per column as you like:

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

The effect is a when= condition, a scale=, or a bar= from the table above. A when= rule needs at least one style: text=#RRGGBB, fill=#RRGGBB, or bold. Colors may add an alpha channel (#RRGGBBAA), and the text after when= can't contain a ;.

-- sqlly conditional: column=Status; when=is Failed; text=#B00020; bold
-- sqlly conditional: column=Status; when=contains warn; fill=#FFF4CE
-- 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;

A tiered column - the first matching rule wins, so the order matters:

-- sqlly conditional: column=Score; when=> 90; fill=#E3F4E8; text=#1E7B34; bold
-- sqlly conditional: column=Score; when=between 50 and 90; fill=#FFF4CE
-- sqlly conditional: column=Score; when=< 50; fill=#FDE7E9; text=#B00020

A score of exactly 90 matches the second rule, not the first: > and < are strict, while between includes both ends.

Directive reference: SQLly directives › Conditional. To change how values are written rather than colored, see Formatting.