Data Analysis with Spreadsheet – JSS3 Digital Technologies Lesson Note (First Term)

Data analysis with a spreadsheet is the process of sorting, filtering, formatting and summarising data so that it gives useful information.

Behavioural objectives

1. Define sorting, filtering, conditional formatting and summary table.
2. Sort spreadsheet data in ascending order, descending order and by more than one level.
3. Filter a list to show only the rows needed.
4. Apply conditional formatting rules to highlight cells.
5. Explain how a summary table or Pivot Table shortens a large data list.

What is sorting in a spreadsheet?

Sorting is the rearranging of data so that it follows a specific order.

• Use: a list of 100 students in random order is hard to search, and sorting lets you find “Zainab” quickly.
• Real-life example: the contact list on a phone is sorted alphabetically, so “Dad” or “Dami” is easy to find.

What are the types of sort?

There are three types of sort: ascending, descending and multi-level.

• A to Z (Ascending): puts names in alphabetical order or numbers from smallest to largest.
• Z to A (Descending): puts the highest scores at the top or names in reverse order.
• Multi-level sort: sorts by one column first, then by another inside each group.

Example: sort by Class first, then sort by Name inside each class.

How do you do a multi-level sort?

1. Click any cell inside your data list.
2. Open the Data tab and click Sort.
3. Under “Sort by”, choose the first column, for example Class.
4. Click Add Level and choose the second column, for example Name.
5. Choose the order (A to Z or Z to A) for each level and click OK.

What is filtering in a spreadsheet?

Filtering is the hiding of rows you do not need, so that only the rows that match your condition are shown.

• Data stays safe: filtering does not delete data, it only hides rows for now.
• Example 1: show only students who have not paid their lab fees.
• Example 2: show only students in the “Blue House” sports team.
• The funnel idea: a kitchen funnel lets liquid through and stops big chunks, and a filter lets only the wanted data flow to your screen.

How do you use a filter?

1. Highlight your headers (Name, Age, Score).
2. Click the Filter button (it looks like a funnel) on the Data tab.
3. Click the small arrow that appears on the column header.
4. Uncheck the items you do not want to see.
5. Click OK. To bring back all rows, click the arrow and choose to clear the filter.

What is the difference between sorting and filtering?

Sorting changes the order of all rows. Filtering changes which rows are visible.

What is conditional formatting?

Conditional formatting is a spreadsheet tool that changes the colour of a cell automatically when its value meets a rule you set.

• Idea: it tells the computer, “If a number meets this rule, change its colour.”
• Comparison: it works like a highlighter that colours cells by itself.
• Where to find it: on the Home tab, under Conditional Formatting.

What are the common conditional formatting rules?

• Highlight Cells Rules: for example, make every score below 40 red, to show who needs extra help.
• Top/Bottom Rules: for example, highlight the top 3 highest scores in green.
• Data Bars: add a coloured bar inside the cell, and the bigger the number, the longer the bar.
• Color Scales: shade cells from one colour to another by value, which gives a “heat map”.

Why use conditional formatting?

• Easy to read: a wall of numbers tires the eyes, and colour makes it clearer.
• Fast checking: you can see problems or successes without reading every number.

What is a summary table?

A summary table is a small, readable table that shows totals, averages or counts taken from a large amount of data.

• Need: after sorting, filtering and formatting, a sheet may still have 500 rows.
• Audience: a Principal wants a short summary, not every row.

What goes into a summary table?

• Total Sales: the sum of sales for the month.
• Average Score: for example, the average for JSS3 compared with JSS2.
• Total Count: for example, the number of boys and girls in the school.

What is a Pivot Table?

A Pivot Table is an advanced spreadsheet tool that turns a long list into a small table of totals.

1. Click any cell inside your data list.
2. Open the Insert tab and click PivotTable.
3. Choose where to place it and click OK.
4. Drag a field such as Category into Rows.
5. Drag a field such as Price into Values to get totals.

Class lab exercise

1. Create a list of 10 imaginary items in a grocery store with the columns Name, Category, Price and Quantity.
2. Sort the items by Price (highest to lowest).
3. Filter the list to show only items in the “Vegetables” category.
4. Use Conditional Formatting to turn any Quantity less than 5 red, to show that stock is running out.

Evaluation

1. Define sorting and name the three types of sort.
2. State two differences between sorting and filtering.
3. Write the steps for filtering a list to show only one category.
4. Give two rules you can set in conditional formatting, with an example of each.
5. What is a Pivot Table used for?

Assignment

Create a spreadsheet of 10 JSS3 students with Name, Class and Score. Sort by Score from highest to lowest, then colour every score below 40 red.