← ACARA Digital Technologies Years 7–8

Using sort and filter together to find top and bottom values

First filter the table to keep only one group, then sort a number column from largest to smallest. The top row is the highest value and the bottom row is the lowest in that group.

Part of: ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 27, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 10

Understanding Using sort and filter together to find top and bottom values in ACARA Digital Technologies Years 7–8

What it does: Filtering first limits the table to Tech. Sorting Z to A then puts Headphones ($45) in the top row. Repeating the same filter and sort for each group lets you compare their top and bottom values fairly. Largest to smallest puts the biggest number at the top and the smallest at the bottom. Without filtering first, the top rows could come from other categories and not just the one you care about. The Tech prices are Headphones 45, Calculator 25 and USB Drive 12. The smallest value, USB Drive, ends up at the bottom. Sort Price Z to A (largest to smallest) so the highest value in the filtered group is in the top row.

Common mistakes: The goal is the dearest Tech item, so the filter must keep the Tech group. Filtering for Sport finds a different group. It should be Filter Category = Tech. The sort is high to low, so the top row has the highest price. The cheapest item is in the bottom row. It should be Read the bottom row.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Using sort and filter together to find top and bottom values to a class →

This module's key terms are also in a printable crossword (Modules 302–324) and word search (Modules 261–324).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

You want the highest-priced Tech item in the shop sheet. What is the best order of steps?

Sort Price smallest to largest, then pick any row at randomFilter Category to Tech, then sort Price largest to smallest

Matches

Sort Price Z to A, then read the top row as the most expensive Tech item

↓

The table was never filtered to Tech, so the top row is the most expensive item of all groups (Backpack, $55), not the top Tech item.

Applying

Meanings

Term

filter then sort

Meaning

First filter the table to keep only one group, then sort a number column from largest to smallest. The top row is the highest value and the bottom row is the lowest in that group.

Practice

=MAX(C6:C7)-MIN(C6:C7)

Testing

Gaps

=SORT((A2:D9,B2:B9="Tech"),3,-1)

Check Answer

Spots

1# Find the dearest Tech item

2Filter Category = Sport

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. You want the highest-priced Tech item in the shop sheet. What is the best order of steps?

A. Filter Category to Tech, then sort Price largest to smallest ✓

B. Sort Price smallest to largest, then pick any row at random

Filtering first keeps only the Tech group. Sorting Price largest to smallest then puts the highest Tech price in the top row.

2. After you filter the marks table to one group and sort a number column largest to smallest, where is the highest value?

A. In the top row of the filtered results ✓

B. In the bottom row of the filtered results

Largest to smallest puts the biggest number at the top and the smallest at the bottom.

3. Which choice finds the top 5 in one category?

A. Sort the whole table, then read the first 5 rows whatever their category

B. Filter to the category, sort largest to smallest, then read the first 5 rows ✓

Without filtering first, the top rows could come from other categories and not just the one you care about.

4. In the shop sheet, you filter Category to Sport and sort Price largest to smallest. Which item is in the top row?

A. Football (30) ✓

B. Water Bottle (9.5)

The Sport items are Water Bottle (9.5) and Football (30). Largest to smallest puts Football first.

5. You filter Category to Tech and sort Price largest to smallest. Which item is in the bottom row?

A. Headphones (45)

B. USB Drive (12) ✓

The Tech prices are Headphones 45, Calculator 25 and USB Drive 12. The smallest value, USB Drive, ends up at the bottom.

Match the Pairs

Filter: Category = Tech → Keeps only the Tech rows and hides all the other rows

Sort: Price, Z to A → Puts the largest price in the top row and the smallest price in the bottom row

To find the most expensive Tech item, keep only the Tech group first, then sort the prices from largest to smallest → Filter Category = Tech, then sort Price Z to A

To compare two groups, filter each group in turn and sort the same number column each time → Filter Category = Sport, sort Price Z to A, then filter Category = Bags, sort Price Z to A

Sort Price Z to A, then read the top row as the most expensive Tech item → The table was never filtered to Tech, so the top row is the most expensive item of all groups (Backpack, $55), not the top Tech item.

Filter Category = Tech, sort Price A to Z, then read the top row as the highest price → A to Z sorts smallest to largest, so the top row shows the lowest Tech price (USB Drive, $12), not the highest.

Key Term

filter then sort

First filter the table to keep only one group, then sort a number column from largest to smallest. The top row is the highest value and the bottom row is the lowest in that group.

Fill in the Blanks

1. =SORT(FILTER(A2:D9,B2:B9="Tech"),3,-1)

Hint: Type the function that keeps only the rows for one group, before the sort is applied.

2. =SORT(FILTER(A2:D9,B2:B9="Tech"),3,-1)

Hint: Type the number that tells SORT to go from largest to smallest.

3. =SORT(FILTER(A2:D9,B2:B9="Tech"),3,-1)

Hint: Type the cell range of the column that holds the category being filtered.

4. =MIN(FILTER(C2:C9,B2:B9="Tech"))

Hint: Type the function that finds the lowest value in the filtered group.

5. =SORT(FILTER(A2:D9,B2:B9="Sport"),3,-1)

Hint: Type the column number (counting from the left of the table) of the Price column to sort by.

Spot the Error

Question 1

A # Find the dearest Tech item

B Filter Category = Sport

C Sort Price largest to smallest

D Top row = highest price

Line B — The goal is the dearest Tech item, so the filter must keep the Tech group. Filtering for Sport finds a different group. It should be Filter Category = Tech.

Question 2

A # Cheapest Sport item

B Filter Category = Sport

C Sort Price high to low

D Read the top row

E Bottom row = lowest price

Line D — The sort is high to low, so the top row has the highest price. The cheapest item is in the bottom row. It should be Read the bottom row.

Question 3

A # Top 5 Blue house students

B Filter House = Blue

C Sort points smallest first

D Read the first 5 rows

Line C — Sorting smallest first puts the lowest points at the top, so the first 5 rows would be the bottom 5. To get the top 5, sort largest first.

Question 4

A Filter Category = Tech

B Sort Item largest to smallest

C Highest price is in row 2

D Top row = highest price

Line B — Item is a text column, so it cannot show the highest or lowest value. You must sort a number column, so it should be Sort Price largest to smallest.

Question 5

A Filter House = Red

B Sort points low to high

C Note the top row points

D Filter House = Blue

E Sort points high to low

F Compare the two top rows

Line B — The next line notes the top row as the highest points. Sorting low to high puts the lowest points at the top. It should be Sort points high to low.

Student with a class code? Join your class →