A rule instead of a hand colour
Conditional formatting = when a cell meets a condition, it changes colour (or bold). When a grade is 4 or 5, the cell turns red. You do not have to look at every cell — the spreadsheet shouts itself.
Format → Conditional formatting.
In Excel Home → Conditional formatting → Highlight cell rules. We start with a simple “greater than” and “equal to”.
A worse grade than 2 (3, 4, 5)
With grades 1 = best. We want to highlight ≥ 3.
-
Sheet
Grades. Select B3:B10 (grades, not the average). -
Format → Conditional formatting → Custom formula or Greater than.
-
The simple path: Greater than → value
2→ format red fill. Grades 3, 4, 5 turn red. -
Or a custom formula:
=B3>=3(the first cell of the range without $ — relative).
When you change 3 to 1, the red disappears.
The average in the range. Do not select the cell with average 1.8 — the rule “greater than 2” will colour it. Only clean grades.
Negative left on pocket money
-
Pocketselect D2:D8 (Left with a formula). -
Conditional formatting → Less than →
0→ red font or pink background. -
Tuesday −15 turns red. Saturday 60 does not.
A colour scale （任意）
Survey B3:B7 counts — Format → Colour scale. More votes = darker green. Nice before a chart. Chart maker will finish it.
Order of rules
More rules on the same range — the first wins. If the colour does not fit, open Conditional formatting → the list of rules → delete the test one.
🏫 Grade average
One sheet, two columns: subject and grade. At the bottom a sum and an average.💰 Pocket money
Rows = days, columns = income / spend / left.📊 Class survey
One question, answers in one column, later a chart.🏆 Mini dashboard
Three numbers at the top and one chart. Numbers as a link, not copied by hand.Now you 💪
- Grades ≥3 (or >2) are visually different.
- Pocket money: negative left is visible without reading every number.
- Changing a grade changes the colour automatically.
- The average under the table is not in the rule by mistake.
- You know where to delete a rule (Format → Conditional formatting).
Check: The spreadsheet itself warns about a problem. Conditional formatting is the first step to a dashboard.
What to take from this lesson
- Conditional formatting = a rule + a look.
- Worse grades or minus in the pocket = red.
- A range without summary cells.
- A change of data = a change of colour.
- Chart maker adds a chart next to the colours.
© 2026 Ing. Martin Polak / AlgoRhino · コンテンツ利用規約