Conditional formatting

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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%

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

Conditional formatting

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=B2_50

Highlight Cells Rules > ____________ 50

Top/Bottom Rules > Top ___ of values

Colour scale: Min = red, ___ = green

=B2>=PERCENTILE($B$2:$B$30,___)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Highlight Less Than 80

B Range: B2:B30

C Note: flags high scores

Question 2

A Range: C2:C41

B Rule: Top 10%

C Top 10% of 40 = 10 cells

Question 3

A Rule: Colour Scale

B Lowest value: red

C Middle value: yellow

D Highest value: red

Question 4

A Goal: flag temps over 35

B Rule: Greater Than 53

C Fill: orange

Question 5

A Goal: spot the lowest 10%

B Range: E2:E41

C Rule: Top 10%

D Fill: light red

Answer Key

Conditional formatting

Section A: Quick Questions

A — Changes a cell's colour or icon automatically when its value meets a rule

B — Highlight Cells Rule: Greater Than 80

B — It stays red, because manual colouring does not follow a rule

A — Shades each cell on a gradient from the lowest to the highest value

A — Top/Bottom Rule: Top 10%

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

=B2>50

Highlight Cells Rules > Greater Than 50

Top/Bottom Rules > Top 10% of values

Colour scale: Min = red, Max = green

=B2>=PERCENTILE($B$2:$B$30,0.9)

Section E: Spot the Error

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.

C — 10% of 40 cells is 4 cells, not 10. The Top 10% rule highlights only the highest tenth of the values.

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.

B — The goal is temperatures over 35, but the rule uses 53 because the digits are swapped. It should be Greater Than 35.

C — The goal is the lowest 10% of values, so the rule must be Bottom 10%. Top 10% would highlight the highest values.