Using sort and filter together to find top and bottom values

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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)

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)

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Filter: Category = Tech		APuts the largest price in the top row and the smallest price in the bottom row
2Sort: Price, Z to A		BFilter Category = Tech, then sort Price Z to A
3To find the most expensive Tech item, keep only the Tech group first, then sort the prices from largest to smallest		CA to Z sorts smallest to largest, so the top row shows the lowest Tech price (USB Drive, $12), not the highest.
4To compare two groups, filter each group in turn and sort the same number column each time		DThe 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.
5Sort Price Z to A, then read the top row as the most expensive Tech item		EKeeps only the Tech rows and hides all the other rows
6Filter Category = Tech, sort Price A to Z, then read the top row as the highest price		FFilter Category = Sport, sort Price Z to A, then filter Category = Bags, sort Price Z to A

Definitions

filter then sort

Fill in the Blanks

Complete each line of code by filling in the missing parts.

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

=SORT(FILTER(A2:D9,B2:B9="Tech"),3,__)

=SORT(FILTER(A2:D9,_____="Tech"),3,-1)

=___(FILTER(C2:C9,B2:B9="Tech"))

=SORT(FILTER(A2:D9,B2:B9="Sport"),_,-1)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A # Find the dearest Tech item

B Filter Category = Sport

C Sort Price largest to smallest

D Top row = highest price

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

Question 3

A # Top 5 Blue house students

B Filter House = Blue

C Sort points smallest first

D Read the first 5 rows

Question 4

A Filter Category = Tech

B Sort Item largest to smallest

C Highest price is in row 2

D Top row = highest price

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

Answer Key

Using sort and filter together to find top and bottom values

Section A: Quick Questions

A — Filter Category to Tech, then sort Price largest to smallest

A — In the top row of the filtered results

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

A — Football (30)

B — USB Drive (12)

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

Section E: Spot the Error

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.

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.

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.

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.

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.