Download the example workbook
This tutorial will demonstrate how to Sort by using Excel Functions, instead of the Sort Tool.
SMALL & LARGE Functions
The SMALL Function returns the k-th smallest number from a set of data:
While LARGE Function returns the k-th largest number:
It can also provide the row number of the cell where the formula was entered in.
= ROW ()
Sort Data In Ascending Order
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
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.