Although Excel has a built-in sort feature, there are times you may need to sort data by formula or in a formula. This article shows *how to use Excel sort function *with **SMALL **and **ROW** functions.

# Syntax

=SMALL( column of list, ROW()-ROW(row of list’s title))

# Steps

- Start with
**SMALL(**function - Select the whole column that contains the unsorted list
**B:B,** - Continue with
**ROW()**function without arguments - Add minus character (
**–**) - Add
**ROW($2:$2)**function with the row number one above the list - Type
**)**to close the SMALL function and finish the formula - Copy and paste down the formula

# How

The **SMALL** function returns the *nth smallest *value from a specified range. It gets the range of values and *n* value as its arguments. When we set an auto-increasing *n* value, the **SMALL** function becomes a perfect tool to sort values by formula.

To create a dynamic *n* value we use the **ROW** function that returns the row number of its own cell or a specified range. To get the first (smallest) value from the list, n should be equal to 1. If we assume that our *sorted* list starts from 3^{rd} row, the **ROW()** function without an argument returns *3*. Obviously we should subtract 2 from this number to get *1*. If the difference between actual row and desired *n *value is 2, our formula would be:

=SMALL(B:B,ROW()-2)

However; using a static value like *2* has a negative effect to formula’s dynamical structure. So to ensure to find the correct value to subtract every time, we can use the **ROW** function again however this time using it with an argument. Selecting one row above the list always returns the difference we need.

=SMALL(B:B,ROW()-ROW($2:$2))

The important point here is to set the row reference as absolute to preserve its value while copying down the list. This will handle Excel sort function without having to use built-in sort feature.