Advanced Spreadsheets and Reporting Documentation
Index

Conditional Formatting

Conditional formats restyle cells by their calculated value on every recalculation — so they work on formula results and live region data alike. Open the rules with Sheet → Conditional Formatting….

Rule Types

Rule Effect
Cell Value Applies the colours (fill, text colour, bold) to cells matching the condition. With both values and both colours set, the fill blends Color → Color 2 across Value..Value 2 instead.
Data Bar Draws a horizontal bar in each cell, proportional to where its value sits between the range's minimum and maximum.
Color Scale Blends the fill from Color (the range's lowest value) to Color 2 (the highest).

Rule Fields

Field Meaning
Range The cells the rule watches, for example B2:B40 (pickable on the sheet with AssistEdit).
Operator Cell Value rules: Greater Than, Greater or Equal, Less Than, Less or Equal, Equal To, Not Equal To, Between. Numbers and dates use all seven; text and boolean values use Equal To or Not Equal To only.
Value / Value 2 (Between/Blend) Thresholds — AL expressions, evaluated per recalculation: 1000, B1, AVERAGE(A1:A10) and Today all work. The value's kind decides what the rule compares — see What a rule compares. Value 2 (Between/Blend) is the upper bound for Between, or the blend's high end, and must be the same kind as Value.
Color / Color 2 (Max) Hex colours like #d3f9d8 (#abc shorthand works). Cell Value: the fill (and the blend's low end). Data Bar: the bar. Color Scale: Color is the low end, Color 2 (Max) the high end.
Text Color, Bold Cell Value only.

Colour fields open the shared palette picker, seeded with the sheet's colour scheme.

What a Rule Compares

A Cell Value rule's threshold decides what kind of cell it matches. Kinds never cross, so a rule aimed at one column's data leaves everything else alone.

Write a Value like The rule matches With operators
1000, B1, AVERAGE(A1:A10) Number cells All seven
2026-01-31, Today, CalcDate('<-30D>', Today) Date cells All seven, Between included
TRUE, FALSE Boolean cells Equal To, Not Equal To
Released, Blocked, 'On hold' Text and enum cells Equal To, Not Equal To

Notes that save a puzzled half-hour:

  • Text matching ignores case and surrounding spaces, like a Business Central filter: released matches Released.
  • Quotes are optional. A word that isn't a formula is taken literally, so Blocked and 'Blocked' behave the same. Quote it when the value contains a space or could read as something else.
  • Enum and option columns are text: a table region shows the caption, so match Open, not the ordinal.
  • Booleans read TRUE / FALSE in table, FlowField and formula cells. Query and SQL regions are the exception — they render booleans as text through the session language, so those columns match Yes / No.
  • A date threshold needs a year. 2026-01-31 and 1-3-26 are dates; 01-03 is text, on purpose, so codes stay codes. Today and any date-valued expression work too.
  • A slicer cell is not a date. A ?DATE or ?RANGE slicer holds its pick as text, everywhere on the sheet — the same reason CalcDate('<1M>', C2) does not work on one. To compare against the picked date, point a one-step Date Range region in One Date mode at the slicer (start [C2], or END([C2]) for a range's end); that cell is a date, and a rule can use it.
  • Mismatched kinds simply never match — including for Not Equal To, which will not paint a column it was never pointed at. If a rule does nothing, the threshold is usually a different kind from the cells.

Behaviour

  • Blank cells never match, whatever the operator — so the unfilled rows under a short region stay clean rather than satisfying every Not Equal To.
  • Error cells never match.
  • The conditional style is an overlay: it wins over the cell's own styling and over region-supplied styling where they conflict, key by key.
  • Rules run after all formulas, so thresholds referencing formula cells see final values.
  • Data bars and colour scales compute their min/max over the numeric cells of the rule's range on every recalculation — the scale adapts as the data changes. A range holding no numbers at all is scaled by its dates instead, oldest to newest, so a date column can carry a heat map.
  • Rule ranges follow row/column inserts and deletes, and rules travel with templates when their range lies fully inside the template rectangle.
  • A rule's work is bounded at 20,000 cells; larger ranges are skipped.

Recipes

  • Exceptions in red: Cell Value, Less Than, 0, text colour #c42b1c.
  • Highlight above average: Cell Value, Greater Than, AVERAGE(B2:B40), fill #d3f9d8.
  • Magnitude at a glance: Data Bar over an amount column, one brand colour.
  • Heat map: Color Scale from #f8d7da to #d3f9d8.
  • Threshold gradient: Cell Value Between 0 and 100 with both colours set — matching cells blend across the span.
  • Overdue dates in red: Cell Value, Less Than, Today, text colour #c42b1c over a due-date column.
  • This month's activity: Cell Value, Between, CalcDate('<-CM>', Today) and CalcDate('<CM>', Today), fill #d3f9d8.
  • Blocked customers: Cell Value, Equal To, Blocked, fill #f8d7da over a status column.
  • Flag the unchecked: Cell Value, Equal To, FALSE, fill #f8d7da over a boolean column (No for a Query or SQL region).

Worked Example: Flagging an Overdue List

A table region renders open invoices in A5:E200, with Remaining Amt. (LCY) in column E and a slicer-driven "as of" date in C2:

Rule Range Setup Effect
1 E5:E200 Data Bar, colour #8fc3ea Bar length shows each invoice's size at a glance.
2 E5:E200 Cell Value, Greater Than, value AVERAGE(E5:E200) * 2, bold, text #c42b1c Invoices more than twice the average size stand out.
3 D5:D200 Cell Value, Less Than, value H2, fill #f8d7da Due dates before the "as of" date turn pink. H2 is a one-step Date Range region in One Date mode started at [C2], which turns the slicer's text pick into a real date cell — so the rule follows the slicer.

Because the thresholds are AL expressions re-evaluated on every recalculation, flipping the C2 slicer refilters the region and recomputes what counts as "unusually large" — the rules adapt to whatever the region currently shows. Rows the region doesn't fill (below the last invoice) are blank, so they simply never match.