Year 7 · Spring 1

Spreadsheets · Lesson 4 of 5

Sorting, filtering and charts

PracticalTopic 18 Data, spreadsheets and AI

Year 7 · Spreadsheets · Lesson 4 of 5

You will learn to

  • Sort a table by one column without mixing up the rows.
  • Use a filter to show only the rows that match, like one house.
  • Choose a bar, line or pie chart for a question, and explain why.

Year 7 · Spreadsheets · Lesson 4 of 5

Success criteria

You have got it when you can say:

  • I can sort the sports-day sheet from highest to lowest score.
  • I can filter the sheet to show one house only.
  • I can make a chart and say why I chose that type.

Year 7 · Spreadsheets · Lesson 4 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last lesson

    To show change over time, use a…

    A Scatter graphB Line chartC Pie chart

    B. Line chart

  2. Last unit

    In a star network the central switch breaks. What happens?

    A Every device loses the networkB Only one device drops offC Data finds another route

    A. Every device loses the network

  3. Earlier this year

    Which is the Spanish equivalent of the Data Protection Act 2018?

    A Ley de Propiedad IntelectualB LOPDGDD (Ley Orgánica 3/2018)C Código Penal art. 264

    B. LOPDGDD (Ley Orgánica 3/2018)

Year 7 · Spreadsheets · Lesson 4 of 5

Key words (1 of 2)

Formula
A spreadsheet calculation. Always starts with =.
Function
A subroutine that returns a value.
Record / field
One row in a database table: everything stored about one thing.
Primary key
A field whose value is unique for every record.

Year 7 · Spreadsheets · Lesson 4 of 5

Key words (2 of 2)

SQL
Structured Query Language: asks databases questions.
Machine learning
The computer finds rules itself from labelled examples.

The explanation · step 1 of 1

Charts

ChartUse it forExample
BarComparing categoriesVotes per club
LineChange over timeTemperature each hour
PieParts of one wholeHow you spend 24 hours
ScatterIs there a link?Revision hours vs score

Year 7 · Spreadsheets · Lesson 4 of 5

Quick quiz · 1 of 3

A face-recognition model works badly on faces unlike its training photos. This is…

  1. A A syntax error
  2. B Overflow
  3. C Bias from unrepresentative training data

C. Bias from unrepresentative training data

The model only learns what it is shown. Missing groups in the data means worse results for those groups.

Year 7 · Spreadsheets · Lesson 4 of 5

Quick quiz · 2 of 3

Every spreadsheet formula starts with…

  1. A #
  2. B $
  3. C =

C. =

The = tells the spreadsheet: calculate this, don't just show it.

Year 7 · Spreadsheets · Lesson 4 of 5

Quick quiz · 3 of 3

=B2*C2 in D2 is filled down to D5. What is in D5?

  1. A =B5*C5
  2. B =E2*F2
  3. C =B2*C2

A. =B5*C5

Relative references move with the formula: 3 rows down, so 2 becomes 5.

Year 7 · Spreadsheets · Lesson 4 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. In Spanish Excel, =IF(B2>=25,"Pass","Retry") becomes…

    A =IF(B2>=25;"Pass";"Retry")B =SI(B2>=25,"Aprobado","Repetir")C =SI(B2>=25;"Aprobado";"Repetir")

    C. =SI(B2>=25;"Aprobado";"Repetir") Spanish name SI, and semicolons, because the comma is the decimal point.

  2. The numbers 3, 9, 4, 4, 10. What is the median?

    A 9B 6C 4

    C. 4 Sort: 3, 4, 4, 9, 10. The middle (3rd) value is 4.