Conditional Formatting panel

A rule formats cells by their value. The panel shows every rule of the report as a card. Open it from Start tab > Styles > Conditional Formatting > Manage Rules... (or New Rule...). The one-click entries of the same menu create rules without opening the panel - they land here as ordinary cards.

📷 Screenshot needed: ccf-panel.png - the panel with three rules, one of them open.

Everything is saved as it happens and drawn in the table at once. There is no Save button. A rule that matches a cell wins over the cell's own static formatting (a rule's format comes after font, fill and borders).

The list

At the top is New rule with three buttons - If ... then, Data bars, Color scale - which start a new rule of that kind for the selected columns. The new rule is saved with its first change (a data bar or color scale rule at once), so closing it unchanged leaves nothing behind.

Below it every rule is a card:

Part	What it does
Switch	Turns the rule on or off. A rule that is off stays saved and in its place.
Name and meta line	The rule's name (or a sentence that describes it, such as "AC 2023 > 100") and what it is for.
Kind icon	Which kind of rule it is.
Duplicate (⧉)	Makes a copy right below it. A rule with a name of its own gets "(copy)" added to it.
Delete (✕)	Deletes the rule. A message offers Undo.
Handle (⋮⋮)	Drag it, or use the up and down arrow keys on it, to change the priority.
Card click	Opens the editor under the card. Only one editor is open at a time.

Priority: the rule at the top of the list wins when several rules match the same cell. New rules from the ribbon go to the top.

Without a license

The free version has room for one rule. It is the topmost rule that is switched on; with a second one in the list (a report made with a license, say) the card of every further rule says Needs a license - not applied and does nothing to the table, but stays saved. New rule, Duplicate and the one-click entries of the ribbon menu do not make another rule while there is one: a message says what the free version has, with Learn more. Deleting the rule leaves room for a new one; switching it off lets the next rule in the list apply. With a license the cards work again and nothing is lost. See License.

The editor

A rule reads as a sentence: who it is for, when it applies, what it does then.

Kind

Three buttons choose the kind: If ... then (a condition), Data bars, Color scale. Switching the kind keeps the rest.

Applies to

The columns the rule is for, shown as chips; + opens the column picker. A rule needs at least one column - until it has one, it is not saved and the empty field is marked.

• The column picker shows every column as a tree (a year, its quarters, the measures under them) with a search box and check boxes. Only columns that show values can be picked. A check box on a group checks all of its columns.
• When the picked columns share one measure, a switch appears: All "Measure" columns, in every period. With it on, the rule follows that measure in every column group, including ones that appear later.

If ... then

Field	Options
When	First what is compared: Selected cell (the cell's own value, the default) or - listed after it - any other column, which takes that column's value in the same row instead; for an "all columns of a measure" rule the list holds the measures (the same period). Then Value, Top / Bottom, Average, Duplicates. When another column is chosen, a sentence under the row says what the choice means for this rule.
For Value	An operator - is greater than, is greater than or equal to, is less than, is less than or equal to, is equal to, is not equal to, is between, is not between, contains, does not contain, begins with, ends with - and one value, or two for between.
For Top / Bottom	Top or Bottom, a count (at least 1), and items or % of the column. The ranking is among the rows that carry their own values - the lowest level of the rows, whether they are open or not. The Total of a group and the grand Total are sums of those rows: they are not ranked and not formatted by Top / Bottom, Average or Duplicates. Rows with the same value as the cut-off all count (two rows tied for 5th place are both formatted).
For Average	Above average, Below average, Equal to or above average, Equal to or below average, and 1, 2 or 3 standard deviations above or below.
For Duplicates	Duplicate values or Unique values.
Then	The format - see below.

Then: the format

Six ready-made formats as chips (the same six Excel offers) and Custom:

Chip	Format
Light red	Light red fill with dark red text
Yellow	Yellow fill with dark yellow text
Green	Green fill with dark green text
Red fill	Light red fill
Red text	Dark red text
Red border	Red border
Custom	A format of your own:

A custom format has:

• Fill - a switch Fill the cell, a color, and an opacity in percent.
• Text - a color (or Own color to go back to the cell's), and B, I, U, S for bold, italic, underline and strikethrough.
• Font - a family and a size; Own font / Own size leave the cell's.
• Borders - for Top, Right, Bottom and Left: a check box, a thickness in px, a color and a style (solid, dotted, dashed).

Data bars

Field	Options
Color	Six preset colors, or Another color
Gradient fill	Switch: gradient or solid bar
Minimum / Maximum (under Advanced)	Lowest value / Highest value, Number, Percent or Percentile; the last three take a value.

Color scale

Field	Options
Presets	Twelve scales (see Conditional Formatting menu)
Colors (under Advanced)	2 colors or 3 colors
Minimum, Midpoint (3 colors), Maximum	Each with a type - Lowest/Highest value, Number, Percent, Percentile (a midpoint has no lowest/highest) and a color. A new midpoint starts at the 50th percentile.

Advanced

A collapsible section, open by default when the rule has a name.

The Compare choice (see When above) is not here: for data bars and color scales it is the Compare row at the top of the rule, for a condition the first dropdown of When. What it means: for a value condition with no value typed, each cell is compared with the other column; for ranks, averages and duplicates, they are taken from the other column; for data bars and color scales the bar or color is taken from that column.

Field	What it does
Name	Your own name for the rule. The field's hint shows the name the list would give it.
Bounds / stops	For data bars and color scales, see above.

The ribbon's one-click rules

See Start tab > Styles. After a one-click rule, a message at the bottom says what was made - for example "Data bars on Sales" - with Undo and Edit; Edit opens the rule's card here. The quick forms apply immediately - no extra step.

note

The panel is wider than the other side panels and closes when you leave Focus Mode or open another panel.