A table that shouts itself
Conditional formatting does not change the number in the cell. It changes colour when a rule is true. Grade 4 and up = a red background. Spend bigger than 50 Kč = orange. You do not have to go through the table with your eyes — the table warns you.
A chart shows the whole picture. Conditional formatting watches single cells. Both belong to the project.
Rules on grades and spend
-
Sheet
Grades. Select B2:B6 (only grades, not the heading). Format → Conditional formatting. -
A rule: Cell is greater than →
3. Style: a light red background or bold font. Grades 4 and 5 (if they were there) turn red. Here a 3 by English — by the rule >3 no, but >2 yes? Fix the line: greater than or equal to 3 if a three = watch out. -
A second rule （可选）: Equal to
1→ a green background. A one = super. -
Sheet
Pocket. Select C2:C8 (Spent). A rule: greater than30→ orange. A day with a big spend lights up. -
Change C4 to
45. The cell recolours. Put it back. The spend chart moved too — two layers of the same truth.
Write rules by the value in the cell, not by the chart colour. A chart does not read conditional formatting — it reads numbers. Formatting is for the table, a chart for presenting.
More rules — order
Sheets applies rules from the top down. Put a stronger rule (red = worst) above a weaker one (yellow = watch out). When a cell meets two rules, the first in the list wins.
On a survey in E2:E5 you can: the biggest count = dark blue. Format → conditional → Custom formula =E2=MAX($E$2:$E$5) — only the winning row highlighted.
What often goes wrong
A rule on the whole column including the average. You colour B8 average 1.8 too. Select only B2:B6.
Text instead of a number. A rule “greater than 3” on a cell with three does not work. Numbers must be numbers.
Too many colours. Three rules are enough. Twelve colours = a rainbow with no sense.
🏫 Grade average — a visual watch
B2:B6 red from grade 3 up, green ones. Average in B8 without formatting — it is a summary, not one grade. When you change a grade, the colour and the average recalculate.
💰 Pocket money — spend
C2:C8 orange over 30 Kč. Column D (Left) can be red when less than 0 — minus in the pocket. A rule: cell less than 0.
📊 Class survey — the winner
E2:E5: a custom formula =E2=MAX($E$2:$E$5) → bold. In the table you at once see which row goes into the chart as “main”.
🏆 Mini dashboard — no duplicates
Leave conditional formatting on Grades and Pocket. The dashboard only links numbers — do not copy a table for colours. One truth, one place for colours.
Now you 💪
First: grades B2:B6 — at least one rule (≥3 or =1).
Second: spend C2:C8 — orange over 30 Kč. Try one day over the line.
Check: The average and headings are not coloured by mistake. A change of a number = a change of colour. The chart still sits on the same data.
What to take from this lesson
- Conditional formatting = colour by a rule, the value does not change.
- Select only data cells, not totals and averages.
- The table watches detail, the chart shows the whole.
© 2026 Ing. Martin Polak / AlgoRhino · 内容使用条款