icon-background-square
New
New
Year 7

Applying conditional formatting to data

I can use conditional formatting and a range of tools to present data effectively.

icon-background-square
New
New
Year 7

Applying conditional formatting to data

I can use conditional formatting and a range of tools to present data effectively.

warning

These resources will be removed by end of Summer Term 2025.

Switch to our new teaching resources now - designed by teachers and leading subject experts, and tested in classrooms.

Lesson details

Key learning points

  1. Conditional formatting applies a style or format to a cell based on whether a cell matches a certain criteria.
  2. Conditional formatting can be applied to specific data in a range of cells.
  3. Conditional formatting can be used to highlight or pick out key pieces of data.
  4. Spreadsheet tools can be combined to analyse and present data.

Keywords

  • Conditional formatting - applies a style or format to a cell based on whether a cell matches a certain criteria

Common misconception

Pupils may think that conditional formatting can only be used to add background colour to a cell.

Conditional formatting can be used to apply a range of formatting a cell including the text of the cell itself.


To help you plan your year 7 computing lesson on: Applying conditional formatting to data, download all teaching resources for free and adapt to suit your pupils' needs...

additional-material

Files needed for this lesson

  • Weather data 145.23 KB (XLSX)
  • UK forestry data 277.38 KB (XLSX)

Download these files to use in the lesson.

Conditional formatting is often seen in the world around us to draw attention to when changes require humans to take notice for example, the crossing at a set of traffic lights. Encourage pupils to identify examples they have seen themselves to appreciate the usefulness of this formatting technique.
speech-bubble
Teacher tip
equipment-required

Equipment

Pupils will require access to a device with software that provides spreadsheet capabilities.

copyright

Licence

This content is © Oak National Academy Limited (2025), licensed on Open Government Licence version 3.0 except where otherwise stated. See Oak's terms & conditions (Collection 2).

Lesson video

Loading...

6 Questions

Q1.
Which tool is used to organise data in ascending or descending order in a spreadsheet?
Correct answer: sort
filter
freeze panes
merge cells
Q2.
Which function would you use to count the number of non-blank cells in a range?
COUNTBLANK
Correct answer: COUNTA
ISNOTBLANK
COUNTALL
Q3.
What does the function =IF(A1>10, "Yes", "No") do?
counts the number of cells greater than 10 in the range A1
Correct answer: checks if the value in A1 is greater than 10 and returns "Yes" or "No"
sorts values greater than 10 into a new range
deletes values in A1 less than 10
Q4.
What does the function =MIN(A1:A10) do?
Correct answer: finds the smallest number in the group of cells A1 to A10
adds all the numbers in A1 to A10
counts how many numbers are in A1 to A10
finds the largest number in A1 to A10
Q5.
Write the formula you would use to multiply the value in A1 by the value in B1.
Correct Answer: =A1B1, =B1A1
Q6.
The function used to add a group of numbers together is = (A1:A10).
Correct Answer: SUM

4 Questions

Q1.
What is the purpose of conditional formatting?
Correct answer: to highlight cells based on specific criteria
to sort and filter data automatically
to validate data entry rules
to protect cells from being edited
Q2.
Which of these is an example of conditional formatting?
Correct answer: highlighting all cells with values greater than 10
changing the colour of a cell manually
adding a border to all cells
Correct answer: changing the font colour of all cells which contain the text 'Yes"
Q3.
Which of these rules can be applied with conditional formatting?
highlight cells equal to "Pass"
change font colour for values less than 50
create a colour scale for numbers in a range
Correct answer: all of the above
Q4.
Match the term to the definition.
Correct Answer:SUM,adds a group of numbers together
tick

adds a group of numbers together

Correct Answer:MIN,finds the smallest number in a range
tick

finds the smallest number in a range

Correct Answer:MAX,finds the largest number in a range
tick

finds the largest number in a range

Correct Answer:conditional formatting,highlights cells based on their values
tick

highlights cells based on their values

Correct Answer:filter,displays only rows that meet certain criteria
tick

displays only rows that meet certain criteria