Level 3 · Advanced: formulas & conditional formatting

3.2Conditional formatting

Goal: write property formulas (color, background, visibility) that highlight what matters, with restraint and consistency.

Estimated time: 10 min · self-paced reading · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Appearance reacts to data: each formatting property has its formula button (x-2) — you write a formula there that returns the property value, evaluated row by row.
The formula returns the right type: a color for a color (crRed, crYellow), a boolean for visibility, a style for bold — e.g. If {Montant} < 0 Then crRed Else crBlack.
Flag, don't decorate: negatives in red, thresholds highlighted, statuses colored — sparingly and with a constant color code (red = problem everywhere).

Running mini-case — Farès makes his balances speak

Farès, management controller in Algiers, sends a weekly statement of customer balances: a black-on-white table where unpaid amounts drown in the mass — management sees nothing. He clicks the formula button (x-2) of the Balance field's font color and writes If {Clients.Solde} < 0 Then crRed Else crBlack, then colors the background yellow when the delay exceeds 30 days. The following Monday, negative balances jump out in red: the collection meeting starts from the real cases instead of re-reading the whole table.

This same case returns in the lesson, the diagram, the script and the 48h challenge.

1. The mechanism: one formula per property (x-2 button)

Conditional formatting changes the APPEARANCE of an object (font color, background color, style, border, size, visibility) according to a CONDITION on the data. The mechanism: for almost every property, a small formula button (“x-2” icon) opens an editor where you write a formula returning the property value — and Crystal Reports applies it dynamically, value by value. Example for the font color of an amount: “If {Commandes.Montant} < 0 Then crRed Else crBlack” — negatives in red, the rest in black.

Key point: the formula is evaluated for EACH occurrence of the object (each row), so appearance varies row by row according to the data. And it must return a value of the property's TYPE: a COLOR for a color (crRed, crBlack, crYellow, crNoColor), a BOOLEAN for visibility (True = suppress via Suppress), a style for bold. Returning the wrong type is the classic beginner failure.

2. Highlighting with intent: common cases and discipline

The cases that really help: negative amounts in red (font color), exceeded threshold highlighted (yellow background when stock drops below 10: If {Stocks.Quantite} < 10 Then crYellow Else crNoColor), colored statuses (“Late” red, “OK” green), large amounts in bold. Choose the RIGHT property: FONT color to tint the figure, BACKGROUND color to highlight the whole cell (more visible), bold STYLE to emphasize without color.

The discipline that separates the pro from the Christmas tree: restraint (highlight what is IMPORTANT — too many conditional colors blur everything) and consistency (the same color code everywhere: red = problem/negative, never the reverse). To distinguish from conditional section suppression (2-5): here the object stays DISPLAYED but changes appearance; there the section appears or disappears. Both combine: color the alert AND only show the alert section when needed.

Common misinterpretation: “Conditional formatting is decoration: the more colors, the more professional the report.” No: it is a SIGNALLING language, not decoration. Each color must answer “what should the eye see first?” — beyond three color codes, nobody reads anything anymore. One color = one message, the same everywhere: that is all.

Property, formula, returned type

Text version of the diagram (SEO / screen readers / translation): 1) Property: Farès targets the Balance field's font color via the x-2 button, not the whole report. 2) Formula: If balance < 0 Then crRed Else crBlack — one color per condition, evaluated row by row. 3) Discipline: red reserved for problems, yellow for the critical threshold, nothing else in color. Lesson: one color = one message, repeated the same way everywhere.

The Christmas tree

“Farès had set seven conditional colors: management asked whether it was a report or a year-end decoration.”

Since then, two colors and not one more

A report that flashes no longer flags anything.

🔎 Concrete example — Balances in red, critical stock in yellow

Farès applies his first two appearance formulas in Crystal syntax.

Balance font: If {Clients.Solde} < 0 Then crRed Else crBlack
Stock background: If {Stocks.Quantite} < 10 Then crYellow Else crNoColor
→ preview: appearance varies row by row.

Reading: the eye lands first on red and yellow — exactly the goal.

Lesson: return a color for a color, a boolean for visibility.

Section vocabulary

Conditional formatting

Changing an object's appearance (color, font, background, visibility…) according to a condition on the data: appearance reacts to values.

Simple example: Farès's negative balances shown in red.

What it is not: a black-on-white table where unpaid amounts drown.

Reflex: as soon as a value must jump out, condition it.

Property formula button (x-2)

Small button next to almost every formatting property; you write the formula returning the property value there.

Simple example: The x-2 of the Balance field's font color.

What it is not: recoloring cells by hand one by one.

Reflex: look for the x-2 before any manual touch-up.

Conditional appearance formula

Formula returning a property value according to a condition, e.g. If {Montant} < 0 Then crRed Else crBlack.

Simple example: Farès's yellow background when stock drops below 10.

What it is not: a formula returning text for a color.

Reflex: always return the type expected by the property.

Per-occurrence evaluation

The formula is evaluated for each occurrence of the object (each row): appearance varies row by row.

Simple example: Each customer balance colored according to ITS value.

What it is not: a single frozen color for the whole column.

Reflex: check in preview that it really varies per row.

Consistent color code

Same meaning of colors throughout the report (red = problem/negative), used sparingly.

Simple example: Red reserved for negative balances and late items at Farès's.

What it is not: red for good results in one table and bad ones in another.

Reflex: freeze 2-3 codes and never change them.

In practice — Apply conditional formatting

Select a field (e.g. Amount), open its format and find the FORMULA button (x-2) next to a property (font color).
Write a conditional formula: If {Montant} < 0 Then crRed Else crBlack → negatives in red.
Try other properties: BACKGROUND color by threshold, VISIBILITY (suppress if condition), bold for a remarkable value.
Check in preview that appearance varies row by row; keep a constant color code (red = problem).

You link appearance to data: the report visually flags key information — an asset for readability and decision-making.

Ready-to-say script — present the report that flags issues (30 s)

“This is no longer a table to re-read in full: negative balances appear in red, delays over 30 days in yellow. Same code everywhere — red always means problem. In the collection meeting we start directly from the flagged cases.”

Say when presenting the report; project the preview with the colors.

Copy-paste template — appearance formulas (Crystal syntax)

// Negatives in red (font color)
If {Clients.Solde} < 0 Then crRed Else crBlack
// Critical stock highlighted (background color)
If {Stocks.Quantite} < 10 Then crYellow Else crNoColor
// Colored status
If {Commandes.Statut} = "Late" Then crRed
Else If {Commandes.Statut} = "OK" Then crGreen Else crBlack

Freelancer

Two well-placed color formulas and your report flags what matters all by itself.

SME in Algiers

Farès colors balances and late items: the collection meeting starts from the red cases.

Project team

Freeze the report's color code in the style guide (red = problem) before multiplying reports.

Beginner

This week: one conditional font color + one conditional background color, tested in preview.

✓ Best practices

Choose the right property: font to tint, background to highlight, bold to emphasize without color.
Always return the expected type (color, boolean, style) — the top cause of failures.
Limit yourself to 2-3 color codes and keep the same meaning throughout the report.

✗ Common pitfalls

Coloring to decorate: beyond three codes, nobody reads anything anymore.
Reversing the code from one table to another (red = good here, bad there).
Confusing conditional formatting (changes appearance) with section suppression (makes disappear).

Key takeaways

CONDITIONAL FORMATTING links appearance to data via a formula on a property (x-2 button), evaluated ROW BY ROW.
E.g. color: If {Montant} < 0 Then crRed Else crBlack — the formula returns the property's TYPE (color, boolean, style).
Top uses: negatives in red, highlighted thresholds, colored statuses, remarkable values in bold.
WITH INTENT and restraint, CONSISTENTLY (red = problem everywhere) — otherwise it is noise.

48h challenge — apply tomorrow

Add 2 appearance formulas to your real report (1 font color + 1 background) and have the color code validated by a colleague.

Section exit checklist

My report flags what matters without a full re-read.
My formulas return the type expected by each property.
My color code is limited and constant throughout.

Self-rating 1-5: would a new reader spot the alerts in ten seconds?do I have more than three color codes in the same report?

← Previous : Formula editor (Crystal/Basic syntax) (V3)Next : Running totals (V3) →

Frequently asked questions

How do I highlight values (negatives, thresholds, statuses)?

Select the object, open its formatting, click the FORMULA BUTTON (x-2) of the wanted property and write the formula. Common cases: negatives in red (font color: If {Montant} < 0 Then crRed Else crBlack); highlighted threshold (background: If {Stock} < 10 Then crYellow Else crNoColor); colored status (If {Statut} = “Late” Then crRed Else If {Statut} = “OK” Then crGreen Else crBlack); large amounts in bold (style). Best practices: choose the RIGHT property (font to tint, background to highlight, bold without color), return the RIGHT TYPE (color for a color — crRed, crYellow… — boolean for visibility), and stay SOBER and consistent.

Conditional formatting or conditional section suppression: what is the difference?

Two complementary formula-based mechanisms. CONDITIONAL FORMATTING acts on the APPEARANCE of an OBJECT (color, bold, background…): the object stays DISPLAYED but changes appearance — use: FLAG a value without hiding anything (negative in red). CONDITIONAL SUPPRESSION (2-5) acts on the VISIBILITY of a SECTION (or an object via Suppress): the section is SHOWN or HIDDEN according to a boolean — use: show only what is RELEVANT (hide zero-amount rows, show an alert only if the condition holds). Remember: one CHANGES THE LOOK of what stays visible, the other MAKES APPEAR OR DISAPPEAR. Combine them: color the alert red AND only show its section when it exists.

Check your understanding

What must a font-color formula return?

Additional resources (optional, to go further)

Crystal Reports conditional formatting (colors, alerts)

YouTubeEN

Conditional formatting formulas Crystal Reports (examples)

YouTubeEN

Level 3 examFresh, harder questions to test what you've learned