Learning Excel: Spill and UNIQUE
Spill
Will wants to show the park's zones on the screen. Twelve rides, but how many zones? One formula makes the whole list, and it adjusts by itself when a zone is added.
One formula, a whole list
=UNIQUE(B2:B13) gives each zone once. That's more than one result, so the list spills into the empty cells below G2.
Only G2 holds the formula. G3 to G6 show the rest of the result: change the formula in G2 and the whole list changes with it.
=UNIQUE(B2:B13)
| 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 | Water | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Adventure | Child height | 105 | ||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Funfair | ||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Kidsland | ||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | Space | ||||||||
| 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 |
Something in the way
A list can only spill into empty cells. Here G4 already holds a note, so G2 shows #SPILL! and the list doesn't appear.
Clear G4 and the list appears again straight away.
=UNIQUE(B2:B13)
| 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 | #SPILL! | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Child height | 105 | |||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | break | ||||||||
| 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 |
Exactly once
UNIQUE has a third argument. With TRUE it shows only what occurs exactly once.
You leave the second argument empty, hence two separators in a row: =UNIQUE(B2:B13,,TRUE).
=UNIQUE(B2:B13,,TRUE)
| 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 | Space | 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
Parents ask which minimum heights there are. Put each height once 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