← VCE Spreadsheets, SQL & Visualisation

Conditional formatting

A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 34, VCE Data Analytics - U3O1 - Data Analytics — Module 126, VCE Data Analytics - U4O1 - Development and Evaluation — Module 14, 9-10 Digital Technologies: Analysing and visualising data — Module 11, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 64, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 107, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 40

Understanding Conditional formatting in VCE Spreadsheets, SQL & Visualisation

What it does: The Top 10% rule is a conditional formatting rule that automatically formats the highest 10 percent of values in the selected range. Conditional formatting only changes how cells look. The data stays the same, but highs, lows and outliers stand out. 'Greater than' colours only the cells with values above the number you choose, so 81 and higher turn green. A colour scale, such as light to dark green, lets you see the size of every value at a glance. The lowest and highest values get the two ends of the gradient. The Top 10% rule highlights the highest-valued cells, which would be about 20 of the 200 days. Bottom 10% would pick the coldest days.

Common mistakes: The note says the rule should flag high scores, so it must use Greater Than 80. Less Than 80 would highlight the low scores instead. 10% of 40 cells is 4 cells, not 10. The Top 10% rule highlights only the highest tenth of the values.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Conditional formatting to a class →

This module's key terms are also in a printable crossword (Modules 21–34) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
What does conditional formatting do in a spreadsheet?
Deletes any cell whose value is too high or too lowChanges a cell's colour or icon automatically when its value meets a rule
Matches
Select one cell, then apply Greater Than 80 to find the high scores in column B
↓
Only one cell was selected, so the rule is not applied to the whole column. Select the full range of scores (B2:B30) first.
Applying
Meanings
Term
Conditional formatting
Meaning
A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.
Practice
=B2>AVERAGE($B$2:$B$30)
Testing
Gaps
=B250
Check Answer
Spots
1Highlight Less Than 80
2Range: B2:B30

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Owl' badge — Wise Coder, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. What does conditional formatting do in a spreadsheet?
A. Changes a cell's colour or icon automatically when its value meets a rule ✓
B. Deletes any cell whose value is too high or too low
Conditional formatting only changes how cells look. The data stays the same, but highs, lows and outliers stand out.
2. Your class test scores are in column B. You want every score above 80 to turn green. Which rule fits best?
A. Highlight Cells Rule: Less Than 80
B. Highlight Cells Rule: Greater Than 80 ✓
'Greater than' colours only the cells with values above the number you choose, so 81 and higher turn green.
3. A student colours a cell red by hand. Later the value changes. What happens to the colour?
A. It updates automatically, because the colour follows the value
B. It stays red, because manual colouring does not follow a rule ✓
Manual colouring is fixed. Conditional formatting re-checks the rule whenever a value changes, so the colour updates by itself.
4. What does a colour scale do to a column of numbers?
A. Shades each cell on a gradient from the lowest to the highest value ✓
B. Gives every cell the same colour no matter what the value is
A colour scale, such as light to dark green, lets you see the size of every value at a glance. The lowest and highest values get the two ends of the gradient.
5. You have 200 rows of daily temperatures. You want to highlight only the hottest 10% of days. Which rule should you use?
A. Top/Bottom Rule: Top 10% ✓
B. Top/Bottom Rule: Bottom 10%
The Top 10% rule highlights the highest-valued cells, which would be about 20 of the 200 days. Bottom 10% would pick the coldest days.

Match the Pairs

Highlight Cells Rules > Greater Than... 80 → Colours every cell with a value above 80, so high scores stand out straight away
Color Scales > Green - Yellow - Red → Shades each cell on a gradient from low to high values so you can see the spread of the data at a glance
Make only the highest 10% of values in a column stand out, such as the top-selling days → Top/Bottom Rules > Top 10% ...
Conditional formatting updates by itself, so a cell turns red when its value goes above the limit → =B2>100 -> red fill
Select one cell, then apply Greater Than 80 to find the high scores in column B → Only one cell was selected, so the rule is not applied to the whole column. Select the full range of scores (B2:B30) first.
Colour cells red by hand with the fill tool to show outliers → Manual colouring does not change when the data changes. A conditional formatting rule should be used so the colour updates automatically.

Key Term

Conditional formatting
A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.

Fill in the Blanks

1. =B2>50
Hint: Type the comparison operator that is true when a score is above 50.
2. Highlight Cells Rules > Greater Than 50
Hint: Type the rule name that colours every value above a chosen limit.
3. Top/Bottom Rules > Top 10% of values
Hint: Type the percentage that picks out only the highest values.
4. Colour scale: Min = red, Max = green
Hint: Type the label for the opposite end of the scale from the lowest value.
5. =B2>=PERCENTILE($B$2:$B$30,0.9)
Hint: Type the decimal cutoff that leaves only the top 10% of values.

Spot the Error

Question 1
A Highlight Less Than 80
B Range: B2:B30
C Note: flags high scores
Line A — The note says the rule should flag high scores, so it must use Greater Than 80. Less Than 80 would highlight the low scores instead.
Question 2
A Range: C2:C41
B Rule: Top 10%
C Top 10% of 40 = 10 cells
Line C — 10% of 40 cells is 4 cells, not 10. The Top 10% rule highlights only the highest tenth of the values.
Question 3
A Rule: Colour Scale
B Lowest value: red
C Middle value: yellow
D Highest value: red
Line D — A colour scale gives the lowest and highest values different colours. If both ends are red, you can't tell highs from lows. The highest value should be green.
Question 4
A Goal: flag temps over 35
B Rule: Greater Than 53
C Fill: orange
Line B — The goal is temperatures over 35, but the rule uses 53 because the digits are swapped. It should be Greater Than 35.
Question 5
A Goal: spot the lowest 10%
B Range: E2:E41
C Rule: Top 10%
D Fill: light red
Line C — The goal is the lowest 10% of values, so the rule must be Bottom 10%. Top 10% would highlight the highest values.

Student with a class code? Join your class →