Conditional formatting

This group makes cells change their look automatically based on their value. Instead of coloring cells by hand, you set a rule over a range and the spreadsheet program applies it live: highlight the cells that are above a number, the duplicates, the top 10, the ones above average; paint a colored gradient, an in-cell bar, or a little icon (traffic light, arrow, rating) in each cell; or highlight cells with your own formula. Two extra instructions manage the rules already on a range (list, reorder, delete) and remove conditional formatting from a range or the whole sheet.

Prerequisite: before using any of these instructions, the workflow must already have an XLSX spreadsheet created or opened (with Create spreadsheet). Every instruction works on a sheet (tab) inside that spreadsheet, identified by name (Sheet1 by default), which must already exist.

These concepts recur across several instructions in this group; they are explained here once.

• Document: indicates which document in the workflow the instruction applies to. If left empty, the current document is used.

• The sheet must already exist: the Sheet name field chooses which tab the instruction works on, and defaults to Sheet1. If no sheet with that name is found, the instruction does nothing and the workflow continues with the next instruction.

• A rule covers a range: each instruction places a rule over a range of cells given as top-left cell:bottom-right cell (A1:A100). The rule then applies to every cell in that range. A single cell is written A1, a column-tall range A1:A100.

• What the rule paints: the background, the letter, or both. The six highlight instructions take a Fill color, a Font color and a Bold switch, and each one only touches what you fill in: leaving Fill color empty and setting Font color paints only the letter, which is what “the amounts over 500 in red” usually means. An empty color leaves that part as it is, so a rule with all three empty paints nothing.

• Several rules can stack on the same range: applying more than one instruction to the same range adds each rule on top of the previous ones; they don’t overwrite each other, they layer.

• Rules are tied to the range they cover: Manage conditional formatting rules and Remove conditional formatting find rules by the range they were created with. To manage a rule created over A1:A100, use A1:A100 — not a sub-range of it. Different ways of writing the same block do match: upper or lower case, dollar signs and the order of the two corners are ignored, so a1:a100, $A$1:$A$100 and A100:A1 all reach the rules created on A1:A100.

• Rule order is meaningful: each new rule is added below the ones already on the sheet, and Manage conditional formatting rules can move a rule up or down to change which one wins when two rules paint the same cell.

• Colors are given as hex (RRGGBB): the color fields in this group take a 6-character hex code — two digits each for red, green, and blue. For example FFFF00 is yellow, FF0000 red, and 63BE7B a green. A leading # is optional. Each instruction’s color field lists its default.

• Dynamic values (@name): in any text field (sheet name, range, color, comparison, formula, icon set type, instruction) you can type @ followed by the name of a value saved earlier in the workflow, and it will be replaced by that value.

• Saving a result (Save results / Save count): Manage conditional formatting rules can report what it found. Fill in Save results with a name to store the list of rules, and Save count to store how many there are; leave them empty to save nothing.

• “From variable” fields: the @name trick only works in text fields. The number fields (Threshold, N, Rule number, the icon thresholds) and the yes/no options (Use three colors, Show value, Top, Use percent, Above average, Entire sheet) each have a companion “from variable” field:

  • If you leave it empty, the value typed in the normal field is used.
  • If you fill it in with the name of a saved value, the engine ignores the normal field and reads the value from that variable.

  This is mainly for chaining instructions (one instruction saves a value, the next one reads it). If you only work with fixed values, you can ignore these fields.

• Apply Color Scale .xlsx
• Apply Data Bar .xlsx
• Apply Icon Set .xlsx
• Create Formula Rule .xlsx
• Highlight Above Below Average .xlsx
• Highlight Cells By Value .xlsx
• Highlight Duplicate Cells .xlsx
• Highlight Top Bottom N .xlsx
• Highlight Unique Cells .xlsx
• Manage Conditional Formatting Rules .xlsx
• Remove Conditional Formatting .xlsx

Apply Color Scale .xlsx

Paints a colored gradient across a range, so low, middle and high values get different colors.

When to use it: to see at a glance where the small and large values are in a column of numbers (a heat-map look), e.g. red for low sales up to green for high.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells the gradient covers (A1:A100). Supports @name	A1:A100	No
Min color	Color for the lowest values, hex RRGGBB. Supports @name	F8696B (red)	No
Mid color	Color for the middle values; leave empty for a two-color scale. Supports @name	FFEB84 (yellow)	No
Max color	Color for the highest values, hex RRGGBB. Supports @name	63BE7B (green)	No
Use three colors	On = three-color scale (min-mid-max); Off = two-color (min-max)	Yes	No
Use three colors (from variable)	Reads the three-colors yes/no from a saved value	(empty)	No

Important behavior: with Use three colors on and a Mid color set, it makes a low-middle-high gradient; otherwise a two-color low-high gradient. The rule is added to the range (stacking on any rule already there).

---

Apply Data Bar .xlsx

Draws a small horizontal bar inside each cell, longer for bigger values.

When to use it: to compare the sizes of numbers in a column without a chart — each cell shows a mini bar proportional to its value.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells that get the bars (A1:A100). Supports @name	A1:A100	No
Bar color	Color of the bar, hex RRGGBB. Supports @name	638EC6 (blue)	No
Show value	On = keep the number visible next to the bar; Off = show only the bar	Yes	No
Show value (from variable)	Reads the show-value yes/no from a saved value	(empty)	No

Important behavior: the shortest bar goes to the smallest value in the range and the longest to the largest. The rule is added to the range (stacking on any rule already there).

---

Apply Icon Set .xlsx

Puts a small icon in each cell (traffic light, arrow, rating, …) depending on where its value falls.

When to use it: to flag values as good/ok/bad or low/medium/high with an at-a-glance symbol, like a green/amber/red traffic light per row.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells that get the icons (A1:A100). Supports @name	A1:A100	No
Icon set type	Which icons to use, chosen from a list (see below)	3TrafficLights1	No
Show value	On = keep the number visible next to the icon; Off = show only the icon	Yes	No
Threshold 1 %	Cut-off (as a percentage 0–100) between the 1st and 2nd icon	33	No
Threshold 2 %	Cut-off between the 2nd and 3rd icon	67	No
Threshold 3 %	Cut-off for the 4th icon (only for 4- and 5-icon sets)	75	No
Threshold 4 %	Cut-off for the 5th icon (only for 5-icon sets)	84	No
Show value (from variable)	Reads the show-value yes/no from a saved value	(empty)	No
Threshold 1 % (from variable)	Reads threshold 1 from a saved value	(empty)	No
Threshold 2 % (from variable)	Reads threshold 2 from a saved value	(empty)	No
Threshold 3 % (from variable)	Reads threshold 3 from a saved value	(empty)	No
Threshold 4 % (from variable)	Reads threshold 4 from a saved value	(empty)	No

Important behavior: Icon set type is picked from a list of 16 sets, so it cannot be typed freely and does not accept @name. Each option starts with how many icons it has (3, 4 or 5): 3 traffic lights (plain and rimmed), 3 arrows (color and gray), 3 signs, 3 symbols (circled and plain), 4 arrows (color and gray), 4 red-to-black, 4 ratings, 4 traffic lights, 5 arrows (color and gray), 5 ratings and 5 quarters. Threshold 3 % is only used by 4- and 5-icon sets, and Threshold 4 % only by 5-icon sets.

---

Create Formula Rule .xlsx

Highlights the cells of a range where your own formula is true.

When to use it: when the built-in highlights aren’t enough and you need a custom condition — for example, highlight a row when another column equals "Overdue".

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells the rule covers (A1:A100). Supports @name	A1:A100	No
Formula	The condition, written from the top-left cell (A1>100). Supports @name	A1>100	No
Fill color	Color for cells where the formula is true, hex RRGGBB. Supports @name	FFFF00 (yellow)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No

Important behavior: the formula is written relative to the top-left cell of the range (as in the spreadsheet program’s “use a formula to decide which cells to format”); it is evaluated for each cell. If Formula is left empty, nothing happens.

---

Highlight Above Below Average .xlsx

Highlights the cells whose value is above (or below) the average of the range.

When to use it: to spot the entries that beat — or fall short of — the average, such as the above-average performers in a list of scores.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells to check (A1:A100). Supports @name	A1:A100	No
Above average	On = highlight cells above the average; Off = below the average	Yes	No
Fill color	Color for matching cells, hex RRGGBB. Supports @name	C6EFCE (green)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No
Above average (from variable)	Reads the above/below yes/no from a saved value	(empty)	No

Important behavior: the average is computed over the whole range; each cell above (or below, when Above average is off) that average gets the fill color.

---

Highlight Cells By Value .xlsx

Highlights the cells that compare to a number a chosen way (greater than, equal, …).

When to use it: to color the cells over a threshold (e.g. amounts greater than 1000) or matching an exact number.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells to check (A1:A100). Supports @name	A1:A100	No
Comparison	The test to apply, chosen from a list	greaterThan	No
Threshold	The number to compare each cell against	0	No
Fill color	Color for matching cells, hex RRGGBB. Supports @name	FFFF00 (yellow)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No
Threshold (from variable)	Reads the threshold number from a saved value	(empty)	No

Important behavior: Comparison is picked from a list (greater than, greater than or equal, less than, less than or equal, equal, not equal), so it cannot be typed freely and does not accept @name. Each cell in the range is tested against Threshold and highlighted when the test is true.

---

Highlight Duplicate Cells .xlsx

Highlights the cells whose value appears more than once in the range.

When to use it: to find repeated entries — duplicate IDs, emails or codes in a column.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells to check (A1:A100). Supports @name	A1:A100	No
Fill color	Color for duplicated cells, hex RRGGBB. Supports @name	FFC7CE (light red)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No

Important behavior: every cell whose value is shared by at least one other cell in the range gets the fill color; values that appear only once are left alone.

---

Highlight Top Bottom N .xlsx

Highlights the highest (or lowest) N cells of a range — or the top/bottom N percent.

When to use it: to mark the best or worst few entries, like the top 5 sales or the bottom 10% scores.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells to rank (A1:A100). Supports @name	A1:A100	No
N	How many cells (or what percent) to highlight	10	No
Top	On = highlight the highest values; Off = the lowest	Yes	No
Use percent	On = N is a percentage (top 10%); Off = a count (top 10 cells)	No	No
Fill color	Color for the selected cells, hex RRGGBB. Supports @name	FFEB9C (light yellow)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No
N (from variable)	Reads N from a saved value	(empty)	No
Top (from variable)	Reads the top/bottom yes/no from a saved value	(empty)	No
Use percent (from variable)	Reads the use-percent yes/no from a saved value	(empty)	No

Important behavior: with Top on it highlights the largest values, off the smallest. With Use percent on, N is read as a percentage of the range (top/bottom N %); off, as a plain count of cells.

---

Highlight Unique Cells .xlsx

Highlights the cells whose value appears exactly once in the range.

When to use it: the opposite of highlighting duplicates — to find the one-off entries that have no repeat.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to apply this to. Supports @name	Sheet1	No
Range	Cells to check (A1:A100). Supports @name	A1:A100	No
Fill color	Color for unique cells, hex RRGGBB. Supports @name	C6EFCE (green)	No
Font color	Color for the letter of matching cells, hex RRGGBB; empty leaves the letter as it is. Supports @name	(empty)	No
Bold	On = the letter of matching cells turns bold	No	No

Important behavior: only the cells whose value occurs a single time in the range get the fill color; repeated values are left alone.

---

Manage Conditional Formatting Rules .xlsx

Lists, reorders or deletes the conditional-formatting rules already attached to a range.

When to use it: to see which rules a range has, change which one wins when rules overlap (by moving it up or down), or remove one rule without touching the others.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to work on. Supports @name	Sheet1	No
Range	The exact range the rules were created on (A1:A100). Supports @name	A1:A100	No
Action	Chosen from a list: List rules (list), Delete rule (delete), Move rule up (moveUp), Move rule down (moveDown) — the stored values tell upper from lower case	list (List rules)	No
Rule number	Which rule to act on, counting from 1 (used by delete/move)	1	No
Rule number (from variable)	Reads the rule number from a saved value	(empty)	No
Save results	Name to store the list of rules under (used by list)	(empty)	No
Save count	Name to store the number of rules under	(empty)	No

Important behavior: Action is picked from a list, so it cannot be typed freely and does not accept @name. List rules stores a description of each rule in Save results and the count in Save count. Delete rule removes the rule at Rule number (and drops the whole block if it was the last one). Move rule up/Move rule down swap that rule’s priority with its neighbor to change which one takes precedence. An out-of-range index does nothing; if the range has no rules, Save count is set to 0. The Range must match the one the rules were created with.

---

Remove Conditional Formatting .xlsx

Removes conditional-formatting rules from a range or from the whole sheet.

When to use it: to clear the highlights/gradients/icons you (or an earlier step) added, either from one range or from the entire tab.

Parameter	Meaning	Default	Required
Document	Document the instruction applies to	(current document)	No
Sheet name	Tab to work on. Supports @name	Sheet1	No
Range	The exact range to clear; leave empty to clear the whole sheet (A1:A100). Supports @name	(empty)	No
Entire sheet	On = remove every conditional-formatting rule on the sheet	No	No
Entire sheet (from variable)	Reads the entire-sheet yes/no from a saved value	(empty)	No

Important behavior: if Entire sheet is on or Range is left empty, every conditional-formatting rule on the sheet is removed. Otherwise only the rules on the exact matching Range are removed; a range that doesn’t match any rule leaves everything unchanged.

---