Excel Practice
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

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.

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

In D2, sort the sales with SORT. Ascending is the default, so the first result is the smallest. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it.

To begin, type it exactly:

=SORT(B2:B7)

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

Every step

  1. 1

    In D2, sort the sales with SORT. Ascending is the default, so the first result is the smallest. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it. To begin, type it exactly: =SORT(B2:B7).

    Hint. One range.

  2. 2

    In D4, sort them descending: SORT with a sort index of 1 and an order of -1.

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

  3. 3

    In D6, the second-largest sales figure. INDEX over the descending SORT, position 2.

    Hint. INDEX, SORT inside, position 2.

  4. 4

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

    Hint. Ascending, third from the bottom.

  5. 5

    In D8, write a formula using INDEX and SORT that gives the name of the top rep. Sort the two-column table A2:B7 by its second column, descending, and read row 1, column 1.

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