Learning Excel: FILTER
Filtering with a formula
Visitors want to know where the queue is short, and parents ask which rides their child can already go on. Will wants those lists on the screen, and they have to stay right every time the queue times change.
FILTER
FILTER shows only the rows that meet a condition. The first argument is what you want to see, the second the condition.
=FILTER(A2:A13,C2:C13>30) gives the rides with a queue of more than 30 minutes. For each row, the condition is TRUE or FALSE.
=FILTER(A2:A13,C2:C13>30)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Whirlwind | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Log Flume | Child height | 105 | ||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Corkscrew | ||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Rocket | ||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | |||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
When nothing is left
If no row meets the condition, FILTER gives #CALC!. A third argument says what to show instead.
No queue is longer than an hour, so =FILTER(A2:A13,C2:C13>60,"none") shows none.
=FILTER(A2:A13,C2:C13>60,"none")
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | none | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Child height | 105 | |||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | |||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | |||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | |||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Two conditions
Put two conditions in brackets and multiply them: =FILTER(A2:A13,(B2:B13="Water")*(C2:C13<30)) gives the water rides with a queue of less than 30 minutes. TRUE counts as 1 and FALSE as 0, so only where both are true is a 1 left.
With + instead of *, one of the two conditions is enough.
=FILTER(A2:A13,(B2:B13="Water")*(C2:C13<30))
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Pedal Boats | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Child height | 105 | |||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | |||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | |||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | |||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Try it yourself
Put the rides from the zone in N1 in G2.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Zone | Queue time | Visitors | Height | Result | Zone | Water | ||||||
| 2 | Whirlwind | Water | 45 | 1,200 | 120 | Max. wait | 20 | |||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Child height | 105 | |||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | |||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | |||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | |||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | |||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | |||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | |||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | |||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | |||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | |||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | |||||||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson