# Sort with SMALL & LARGE Functions in Excel & Google Sheets

Written by

Reviewed by

Download the example workbook

*This tutorial will demonstrate how to Sort by using Excel Functions, instead of the Sort Tool.*

**SMALL & LARGE Functions**

We will use the SMALL (or LARGE) function along with the ROW Function to sort our data.

The SMALL Function returns the k-th smallest number from a set of data:

`=SMALL(B2:B8, 3)`

While LARGE Function returns the k-th largest number:

`=LARGE(B2:B8, 3)`

We will use the SMALL Function to sort in ascending order and the LARGE Function to sort in descending order.

**ROW Function**

We also need to use the ROW Function. The ROW Function returns the row number of a cell reference.

`= ROW(C8)`

It can also provide the row number of the cell where the formula was entered in.

`= ROW ()`

**Sort Data In Ascending Order**

We can sort data in ascending order using the SMALL Function and the ROW Function like this:

`=SMALL($A$2:$A$9, ROW()-1)`

**How does the formula work?**

Remember this is our formula:

`=SMALL(A2:A9, ROW() - 1)`

It works by using the to enter the correct k-value for each row. To sort the data in ascending order, the k-value should be 1 for the first cell and then increase as we go down the column.

In the first cell,

```
= SMALL(A2:A9, ROW(B2) - 1)
= SMALL (A2:A9, 2 - 1)
= SMALL (A2:A9, 1)
```

Similarly in the second cell,

```
= SMALL(A2:A9, ROW(B3) - 1)
= SMALL (A2:A9, 3 - 1)
= SMALL (A2:A9, 2)
```

In this way, the k-value increases as we go down the column, outputting the numbers from smallest to largest.

**Sort Data In Descending Order**

Similarly, we can sort data in descending order using the LARGE Function and the ROW Function. It works exactly like the formula above.

`=LARGE($A$2:$A$9, ROW()-1)`

Note: The above formula uses absolute references (the $ signs) to lock cell references when copying formulas. If you aren’t familiar with this, please read our Excel References Guide.

## Google Sheets –Sort with SMALL & LARGE Functions

All of the above examples work exactly the same in Google Sheets as in Excel.