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.
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 | Value, Top / Bottom, Average, Duplicates |
| 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. |
| 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 compares with a column or has a name.
| Field | What it does |
|---|---|
| Compare with column (or Compare with a measure, for an "all columns of a measure" rule) | Check the condition against another column's value in the same row instead of the cell's own value. A sentence under the field says what the choice means for this rule: 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. |
| 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.
The panel is wider than the other side panels and closes when you leave Focus Mode or open another panel.