Conditional formatting answers the reader's first question — where should I look? — before you write a single word of commentary. In RixSheets, rules watch your ranges continuously: pick a rule, define the condition and the look, and the app repaints cells the moment values change. Overdue invoices glow red, top performers shade green, duplicate order numbers light up, and a column of percentages becomes a readable heat map with a color scale.
The rule types and when to use each
RixSheets covers the classic set, and each type has a job it is best at:
| Rule type | What it does | Good example |
|---|---|---|
| Highlight cells | Fill or color values meeting a condition | Red fill on amounts over budget |
| Color scales | Gradient across the range, low to high | Two-color scale on a margin column |
| Data bars | Length bars inside each cell | Bar length equals units sold |
| Icon sets | Symbols by tier: arrows, flags, ratings | Traffic lights on project status |
| Duplicates | Marks repeated values in the range | Spotting double-entered invoices |
| Blanks | Flags empty cells in required columns | Missing region codes before a pivot |
Create your first rule
- Select the range to watch — a column, a table or the whole value area of a pivot.
- Open the conditional formatting menu on the Home tab.
- Pick a rule type. For a first try, choose Highlight cells, then greater than, and enter a threshold such as 10000.
- Choose the formatting — a green fill, bold text or both — and confirm.
- Scan the sheet: every qualifying cell is now shaded, and will stay correct as numbers change.
Rules stack. A column can carry a red rule for negatives and a green rule for above-target values at the same time, each with its own priority.
One choice matters more than it seems: what the rule watches. Applying the rule to the whole table shades entire rows when a single column qualifies — useful for a task tracker, overwhelming for a financial table. Applying it to one column keeps the signal narrow. If the highlight fires but the reader has to hunt for why, scope the rule tighter; if the highlight seems to miss cells, check whether the range starts and ends where you think it does.
Work with the rule list
Complex sheets accumulate rules, and the rule manager keeps them honest:
- Select a cell inside the formatted range and open the rule manager from the conditional formatting menu.
- Review the rules that apply, in evaluation order.
- Reorder with the up and down controls when two rules overlap — the first match that applies formatting wins.
- Edit a threshold, change a color, or untick a rule to disable it without deleting.
- Delete rules you no longer need so the sheet stays predictable.
A rule that made sense in March can quietly mislead in September. Reviewing the list takes a minute and prevents a stale highlight from steering a decision.
Color scales, bars and icons in practice
The gradient rules look decorative and are not — each encodes a comparison the reader would otherwise do by hand:
- Color scales rank a range at a glance. A two-color scale from white to blue on a margin column makes the best and worst rows findable without sorting. Use them when relative position is the message.
- Data bars preserve magnitude inside the cell, so a table keeps its numbers and gains a bar chart at once. They shine in inventory and budget tables where exact values stay important.
- Icon sets bucket values into tiers — the reader gets "on track, watch, behind" without parsing a single number. Keep to three or four tiers; more buckets are harder to decode than raw numbers.
All three update live as values change, and all three print and export through the PDF pipeline exactly as they appear on screen, so the printed report keeps the visual language.
Patterns that earn their keep
- Variance columns: a three-color scale on actual minus plan turns a column of deltas into an instant heat map.
- Aging report: layered highlights — amber over 30 days, red over 60 — on the days-outstanding column.
- Duplicates before cleanup: run the duplicate rule on key columns, fix what you find, then use duplicate removal to finish; the full cleanup flow is in sort, filter and dedupe.
- Dashboards: pair data bars on a KPI table with a chart next to it; see charts in RixSheets.
- Pivot heat maps: apply a color scale across the value area of a pivot and the shading survives refreshes; see pivot tables.
Formatting versus Format Cells
Keep the two straight: Format Cells sets static appearance — fonts, borders, number formats — and nothing changes unless you change it; see the Format Cells dialog. Conditional formatting is dynamic and rule-driven. Use static formatting for identity (currency style, column widths) and conditional formatting for meaning (this value is a problem, that one is a winner).
What to explore next
- Sort, filter and dedupe — act on the duplicates the rule finds.
- Charts in RixSheets — turn highlighted trends into pictures.
- Pivot tables in RixSheets — heat-mapped summaries that refresh with the data.
- Format Cells dialog — the static layer under the rules.