Year 7

Year 7

# Conditional formatting

## Lesson details

### Key learning points

1. In this lesson, we will discover how to use conditional formatting, whereby the appearance of a cell changes automatically depending on the data it contains, according to rules you set. We then complete an end-of-unit summative assessment.

### Licence

This content is made available by Oak National Academy Limited and its partners and licensed under Oak’s terms & conditions (Collection 1), except where otherwise stated.

## Video

Share with pupils

## Worksheet

Share with pupils

## Starter quiz

Share with pupils

### 5 Questions

Q1.
How is data sorted in a spreadsheet?
B-C or Z-A
Only A-Z
Q2.
What does filtering in a spreadsheet allow you to do?
Filtering allows you to add data
Filtering allows you to delete specific data
Correct answer: Filtering allows you to find specific data
Q3.
Which AVERAGE formula is correct?
=AVERAGE(D:D9)
AVERAGE(D4:D9)
Q4.
What does the COUNTIF formula do?
Correct answer: Counts how many of a certain item there are
Counts the column
Counts the rows
Q5.
Which is the correct IF formula?
=IF( “Well Done”, “Try again” D4=3,)
Correct answer: =IF(D4=3, “Well Done”, “Try again”)
IF(D4=3, “Well Done”, “Try again”)

## Exit quiz

Share with pupils

### 11 Questions

Q1.
What symbol must you use at the beginning of a cell when you want to use a function or a formula in that cell?
-
+
Q2.
Name a function that tells you the maximum value.
MAXIMUM
Maximus
Q3.
Name a function that tells you the minimum value.
MINIMUM
MINUS
Q4.
Name a function that tells you how many non-blank values there are.
COUNTIF
MAX
MIN
Q5.
What would be the easiest way to total several values together?
=(D1+D2+D3+D4)
4+2+5+3+1
Q6.
Can you give the cell references for each of the following range in cells row 15–20 in column A
A17:A16
B2:B7
Q7.
Which is the correct IF formula?
=IF( “Well Done”, “Try again” D4=3,)
Correct answer: =IF(D4=3, “Well Done”, “Try again”)
IF(D4=3, “Well Done”, “Try again”)
Q8.
Which AVERAGE formula is correct?
=AVERAGE(D:D9)
AVERAGE(D4:D9)
Q9.
What is Data?
Data can be anything.
Correct answer: Data is facts and figures.
Data is just symbols.
Q10.
What is a secondary source?
Correct answer: A secondary source is a source which you have found and may decide to use.
A secondary source is the same as a primary source, except it looks prettier, like a chart.
A secondary source is when you decide to carry out a survey.
Q11.
What is conditional formatting?
Correct answer: This is when the spreadsheet highlights cells of a similar criteria.
This is when the spreadsheet moves the data to the correct place.
This is when the spreadsheet selects the cells it wants to highlight