Learning Excel: SORT and SORTBY
Sorting with a formula
On the screen at the entrance, Will wants the shortest queue at the top. The table itself has to stay as it is: the tills use that order. A formula makes a sorted copy.
SORT
SORT gives a sorted copy of a range. =SORT(A2:D13,3) sorts on the third column of the range, the queue time, from small to large.
With -1 as the third argument, you sort from large to small: =SORT(A2:D13,3,-1).
=SORT(A2:D13,3)
| 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 | Water | 5 | 400 | Max. wait | 20 | |||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Carousel | Kidsland | 5 | 600 | Child height | 105 | |||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Dodgems | Funfair | 10 | 650 | |||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Mini Train | Kidsland | 10 | 500 | |||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | Teacups | Funfair | 10 | 700 | |||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | Big Wheel | Funfair | 15 | 900 | |||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | Haunted Castle | Adventure | 25 | 800 | |||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | Pirate Ship | Adventure | 30 | 1100 | |||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | Log Flume | Water | 35 | 1500 | |||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | Rocket | Space | 40 | 1300 | |||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | Whirlwind | Water | 45 | 1200 | |||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | Corkscrew | Adventure | 50 | 1700 | |||||
| 14 |
SORTBY
Want only the names, sorted on a column you don't show? Then you use SORTBY: first what you want to see, then what you sort by.
=SORTBY(A2:A13,D2:D13,-1) gives the names, the most visitors at the top.
=SORTBY(A2:A13,D2:D13,-1)
| 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 | Corkscrew | Max. wait | 20 | ||||||
| 3 | Haunted Castle | Adventure | 25 | 800 | 100 | Log Flume | Child height | 105 | ||||||
| 4 | Log Flume | Water | 35 | 1,500 | 110 | Rocket | ||||||||
| 5 | Dodgems | Funfair | 10 | 650 | 130 | Whirlwind | ||||||||
| 6 | Big Wheel | Funfair | 15 | 900 | 90 | Pirate Ship | ||||||||
| 7 | Pirate Ship | Adventure | 30 | 1,100 | 110 | Big Wheel | ||||||||
| 8 | Pedal Boats | Water | 5 | 400 | 85 | Haunted Castle | ||||||||
| 9 | Corkscrew | Adventure | 50 | 1,700 | 140 | Teacups | ||||||||
| 10 | Mini Train | Kidsland | 10 | 500 | 85 | Dodgems | ||||||||
| 11 | Rocket | Space | 40 | 1,300 | 130 | Carousel | ||||||||
| 12 | Carousel | Kidsland | 5 | 600 | 85 | Mini Train | ||||||||
| 13 | Teacups | Funfair | 10 | 700 | 95 | Pedal Boats | ||||||||
| 14 |
Try it yourself
Sort A2:D13 in G2 by visitors, the fewest at the top.
| 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