Sort with SMALL & LARGE Functions in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 22, 2023
Download Example Workbook

Download the example workbook

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

Sort with SMALL LARGE Main

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)

EX 02

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

= ROW ()

EX 03

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)

EX 04

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)

EX 05

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.

Sort with SMALL & LARGE Google

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List