How to use Excel sort function

by | Jul 9, 2018 | Excel Tips & Tricks

How to sort values by formula

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

  1. Start with SMALL( function
  2. Select the whole column that contains the unsorted list B:B,
  3. Continue with ROW() function without arguments
  4. Add minus character (-)
  5. Add ROW($2:$2) function with the row number one above the list
  6. Type ) to close the SMALL function and finish the formula
  7. 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 3rd 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.