Conditional Formatting

Witan supports Excel conditional formatting as workbook rules attached to worksheet ranges. Use conditional formatting to make outliers, thresholds, trends, exceptions, and input states visible without changing the underlying cell values.

Conditional formatting is a workbook feature, not a standalone verifier. Scripts can inspect rules, add or replace rules, remove rules by index, and render the visible result with Witan Render.

Supported Rules

Rule family	What it covers
Cell value	Comparison rules: equal, not equal, greater than, greater than or equal, less than, less than or equal, between, and not between
Text	Contains, does not contain, begins with, and ends with
Blank and error	Contains blanks, does not contain blanks, contains errors, and does not contain errors
Expression	Formula-backed rules evaluated relative to the target range
Time period	Today, yesterday, tomorrow, last 7 days, this/last/next week, and this/last/next month
Top and bottom	Top or bottom rank, by item count or percent
Average	Above average, below average, equal-average variants, and standard deviation thresholds
Duplicate and unique	Duplicate-value and unique-value rules
Color scales	Two-color and three-color scales with min, max, number, percent, percentile, and formula thresholds
Data bars	Positive and negative bars, value display, gradients, borders, axis position, direction, and threshold options
Icon sets	Icon-set style metadata is represented; icon glyphs are not currently drawn in range renders

Rules can include differential styles for fills, fonts, borders, number formats, alignment, and protection where those styles apply to the rule type. Color scales, data bars, and icon sets carry their own visual configuration instead of a normal differential style.

Inspect Rules

Use getConditionalFormatting to list the rules on a sheet:

witan xlsx exec report.xlsx --expr 'await xlsx.getConditionalFormatting(wb, "Summary")'

Each returned rule includes:

index: the sheet-local rule index, used by removeConditionalFormatting.
address: the target range or multi-area range.
type: the rule family.
priority and stopIfTrue: Excel rule ordering behavior where present.
Rule-specific fields such as operator, formula, formula2, text, rank, timePeriod, scale thresholds, data bar options, or icon set style.
style: the differential style for rule types that apply cell formatting.

Add Rules

Use setConditionalFormatting to append rules to a sheet:

witan xlsx exec report.xlsx --save --stdin <<'WITAN'
await xlsx.setConditionalFormatting(wb, "Summary", [
  {
    address: "B2:B20",
    type: "cellValue",
    operator: "greaterThan",
    formula: "100000",
    style: {
      fill: { color: "#E2F0D9" },
      font: { color: "#375623", bold: true }
    }
  }
])
WITAN

Use formula-backed expression rules when the condition depends on nearby cells:

await xlsx.setConditionalFormatting(wb, "Summary", [
  {
    address: "D2:D20",
    type: "expression",
    formula: "=$D2<$C2",
    style: {
      fill: { color: "#FCE4D6" },
      font: { color: "#9C0006" }
    }
  }
])

Use color scales or data bars for quantitative ranges:

await xlsx.setConditionalFormatting(wb, "Summary", [
  {
    address: "E2:E20",
    type: "threeColorScale",
    lowValue: { type: "min", color: "#F8696B" },
    midValue: { type: "percentile", value: 50, color: "#FFEB84" },
    highValue: { type: "max", color: "#63BE7B" }
  },
  {
    address: "F2:F20",
    type: "dataBar",
    dataBar: {
      fillColor: "#4472C4",
      negativeFillColor: "#C00000",
      axisPosition: "automatic"
    }
  }
])

Replace And Remove Rules

By default, setConditionalFormatting appends rules to the sheet:

await xlsx.setConditionalFormatting(wb, "Summary", rules)

Pass { clear: true } to replace all existing rules on the sheet with the provided rules:

await xlsx.setConditionalFormatting(wb, "Summary", rules, { clear: true })

To remove rules, read the current rules, choose their index values, and pass those indices to removeConditionalFormatting:

const rules = await xlsx.getConditionalFormatting(wb, "Summary")
const stale = rules
  .filter(rule => rule.address === "B2:B20")
  .map(rule => rule.index)

await xlsx.removeConditionalFormatting(wb, "Summary", stale)

removeConditionalFormatting removes by index and preserves the relative order of the remaining rules.

Multi-Area Rules

A rule can target a union of concrete A1 ranges on the same sheet:

await xlsx.setConditionalFormatting(wb, "Summary", [
  {
    address: "A1:A10,D1:D10",
    type: "duplicateValues",
    style: { fill: { color: "#FFF2CC" } }
  }
])

Conditional formatting addresses must resolve to concrete ranges on the sheet passed to setConditionalFormatting. Defined-name targets are not accepted for rule addresses.

Rendering

Witan Render evaluates conditional formatting before drawing cells, so rendered ranges show matching fills, fonts, borders, number formats, color scales, and data bars.

Icon-set rules are represented through the conditional formatting model, but icon glyphs are not currently drawn in range renders.

Use a render or diff after changing conditional formatting:

witan xlsx render report.xlsx -r "Summary!A1:F20"

Notes And Limits

Conditional formatting rules are stored as normal Excel workbook rules and round-trip through .xlsx files.
Rule priority and stopIfTrue are preserved for Excel workbooks.
Invalid rule payloads are rejected before mutation, including when { clear: true } is used.

See xlsx API for exact TypeScript and Python signatures.

Charts

Data Validation