The SORT Function in Excel – Streamlining Data Sorting

Sorting in Excel can sometimes be troublesome. Incorrectly selected ranges can lead to formulas becoming mixed up, and we’re not always certain that we’ve chosen the right range. In such cases, the SORT function comes to the rescue, and its operation will be described below.

First, let’s take a closer look at the syntax of the SORT function:

  • array – the range to be sorted,
  • sort_index – the number indicating the row or column in the array to be sorted;
  • sort_order – the sorting order (1 for ascending, -1 for descending order);
  • by_col – a logical argument indicating the sorting direction – by default, it takes the value 0 [FALSE] and sorts by columns, while the value 1 [TRUE] designates sorting by rows.

Now, let’s consider the following example:

In cell M7, I entered the SORT formula. Our task is to sort the results from the table based on the highest average wage in 2019. So, what does our formula look like? Let’s break down this example below:

  • array – the range is the Table2 (F6:K22) table;
  • sort_index – we sort by the sixth column (2019);
  • sort_order – we sort in descending order (-1);
  • by_col – the argument is omitted, so it defaults to 0 [FALSE] (sorting by columns).

The result of the SORT function is a sorted table, which constitutes a spilled range. You can recognize spilled ranges by the blue border that appears around the range when a single cell within it is selected (image above). From now on, adding any new record to the Table2 will automatically be captured by our SORT function and undergo automatic sorting. This is another simple and quick step towards automating our reports.

Interesting article? Share further!

Leave A Comment