Lessons Lesson 86

SORT

About this lesson

SORT returns a range in order as an array, without changing the original. It takes the column to sort by, and 1 or -1 for the direction. INDEX reads any position of the result.

By the end you can

  • sort a range with a formula, ascending or descending.
  • read the second-largest value, or the top name, from a sorted table.

Practises SORT INDEX LARGE UNIQUE

By Ali, Founder, Excel Lessons

The idea

The Sort button rearranges the data. SORT the function leaves the data alone and returns a sorted copy. The copy updates itself when a number changes. Three arguments: the range, the column to sort by, and 1 or -1 for the direction.

Sort the whole table, not just a column, whenever the answer is a name. SORT(A2:B7,2,-1) orders the reps by their sales and keeps each name with its number. So INDEX(…,1,1) is the top rep's name, the question MAX cannot answer. And this grid shows the first cell of the result. Excel 365 would spill all of it.

The mistake to watch for

Sorting a column when the answer is a name. SORT over the sales alone loses which rep each figure belongs to. SORT over the whole table by its sales column keeps every name with its number. Then INDEX row 1, column 1 is the top rep. The direction argument decides what position 3 means: third smallest ascending, third largest descending.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Ascending is the default order.

In D2, type:

=SORT(B2:B7)

D2
Row A B C D E F G
1 Rep Sales Smallest first Largest first Second largest
2 Fay 520
3 Ade 460 Top rep
4 Hal 700
5 Bea 180
6 Cal 310
7 Gus 240
8

Every step

  1. 1

    Ascending is the default order. In D2, type: =SORT(B2:B7).

    Hint. One range.

  2. 2

    The second argument picks the column to sort by, the third the direction. In E2, type: =SORT(B2:B7,1,-1).

    Hint. 1 for the column, -1 for descending.

  3. 3

    INDEX takes one value out of an array. In F2, take the second value from a SORT in largest-first order.

    Hint. SORT goes inside INDEX, at position 2.

  4. 4

    Read =INDEX(SORT(B2:B7),3) and say what it returns.

    Hint. Ascending, third from the bottom.

  5. 5

    In F4, find the top rep's name with INDEX around SORT.

    Hint. SORT(A2:B7,2,-1), then INDEX 1,1.