Colouring cells by their number with a conditional formatting rule

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. What does a conditional formatting rule do?

A It colours a cell automatically when its number matches a condition

B It colours a cell only when you click the fill bucket each time

2. A rule says: if a cell is equal to 1, fill it yellow. A cell contains 1. What colour is it?

A Yellow

B White

3. A rule says: if a cell is equal to 1, fill it yellow. A cell contains 0. What happens?

A The rule colours it yellow anyway

B The rule does not colour it, so it stays the same

4. You change a cell from 0 to 1 and the rule says 'equal to 1 = yellow'. What happens to the cell?

A It stays white until you colour it by hand

B It turns yellow by itself

5. Before you make a rule, what should you do first?

A Select the cells the rule will apply to

B Type the number 1 in every cell on the sheet

Match the Pairs

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

Left column		Right column
1Apply to range: A1:H8		AIs equal to 1 → Fill colour: yellow
2Format cells if... Is equal to 1		BA rule that colours a cell when the number in it is exactly 1
3The rule picks a fill colour, so every cell equal to 1 turns yellow by itself		CChooses which cells the conditional formatting rule works on
4Changing the number in a cell changes its colour, because the rule checks the number again		DType 0 in a yellow cell (rule: equal to 1) → yellow goes away
5Apply to range: A1:H8, Is equal to 1, then colouring each cell by hand		EThe range is too small, so the rule only works on two cells and the rest of the grid will not colour.
6Apply to range: A1:A2, Is equal to 1, Fill: yellow (picture grid is A1:H8)		FColouring each cell by hand is not conditional formatting. A rule should do the colouring when the number matches.

Definitions

conditional formatting

Fill in the Blanks

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

# Colour a cell when it matches 1 Condition: ___________ 1 Fill: yellow

# Cells equal to 0 turn white Condition: Is equal to _ Fill: white

# Cells equal to 1 turn yellow Condition: Is equal to 1 Fill: ______

# The rule covers the grid A1:D4 Range: _____ Condition: Is equal to 1

Rule: Is equal to 1, fill yellow Type 0 in B2: cell turns white Type _ in B2: cell turns yellow

Spot the Error

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

Question 1

A Select the cells A1:H8

B Rule: Is equal to yellow

C Fill colour: Yellow

Question 2

A Select the colour B2:F6

B Rule: Is equal to 0

C Fill colour: Blue

Question 3

A Select the cells A1:E5

B Rule: Is equal to 1

C Fill colour: 1

Question 4

A Click the cells to colour

B Open Conditional formatting

C Choose Is greater than

D Type 1 and pick Yellow

Question 5

A Select just the cell A1

B Rule: Is equal to 1

C Fill colour: Yellow

Answer Key

Colouring cells by their number with a conditional formatting rule

Section A: Quick Questions

A — It colours a cell automatically when its number matches a condition

A — Yellow

B — The rule does not colour it, so it stays the same

B — It turns yellow by itself

A — Select the cells the rule will apply to

Section B: Match the Pairs

Apply to range: A1:H8 → Chooses which cells the conditional formatting rule works on

Format cells if... Is equal to 1 → A rule that colours a cell when the number in it is exactly 1

The rule picks a fill colour, so every cell equal to 1 turns yellow by itself → Is equal to 1 → Fill colour: yellow

Changing the number in a cell changes its colour, because the rule checks the number again → Type 0 in a yellow cell (rule: equal to 1) → yellow goes away

Apply to range: A1:H8, Is equal to 1, then colouring each cell by hand → Colouring each cell by hand is not conditional formatting. A rule should do the colouring when the number matches.

Apply to range: A1:A2, Is equal to 1, Fill: yellow (picture grid is A1:H8) → The range is too small, so the rule only works on two cells and the rest of the grid will not colour.

Section C: Definitions

conditional formatting — A rule that colours a cell by itself when its number matches a condition, like a cell equal to 1 turning yellow. Change the number and the colour changes too.

Section D: Fill in the Blanks

# Colour a cell when it matches 1
Condition: Is equal to 1
Fill: yellow

# Cells equal to 0 turn white
Condition: Is equal to 0
Fill: white

# Cells equal to 1 turn yellow
Condition: Is equal to 1
Fill: yellow

# The rule covers the grid A1:D4
Range: A1:D4
Condition: Is equal to 1

Rule: Is equal to 1, fill yellow
Type 0 in B2: cell turns white
Type 1 in B2: cell turns yellow

Section E: Spot the Error

B — The rule checks the number in the cell, so the condition must be a number like 1. Yellow is the fill colour, which is chosen on the next line. It should be 'Rule: Is equal to 1'.

A — You select the cells the rule applies to first. You choose the colour later. It should be 'Select the cells B2:F6'.

C — The fill colour must be a colour, like Yellow. The number 1 is what the rule looks for in the cell. It should be 'Fill colour: Yellow'.

C — This rule needs the 'Is equal to' condition so only cells equal to 1 turn yellow. 'Is greater than' would colour other numbers too. It should be 'Choose Is equal to'.

A — The rule only works on the cells you select. To colour every 1 in the grid, select the whole grid A1:D4, not just A1.