Ordering Results With SORT

Sorting used to mean selecting your data and clicking a button every time it changed. The SORT function does it with a formula instead, so the order updates automatically whenever the source data changes.

SORT is a dynamic array function: you type it once in a single cell and the results spill down (and across) into the cells below. You never have to re-sort manually again.

SORT takes up to four arguments:

• array the range or array you want to sort
• sort_index which column (or row) to sort by, counting from 1
• sort_order 1 for ascending, -1 for descending
• by_col TRUE to sort columns side to side, FALSE (the default) to sort rows top to bottom

Only the first argument is required. Leave the rest off and SORT sorts the first column ascending.

Imagine sales figures in A2:A8. To list them smallest to largest, point SORT at the range and accept all the defaults.

The formula spills the sorted values down a fresh column. If a number in A2:A8 changes, the spilled list re-orders itself instantly. No buttons, no menus.

To reverse the order, set sort_order to -1. The first argument stays the same, you just add the descending flag.

This is perfect for leaderboards or top-revenue lists where the biggest values belong at the top.

SORT is not limited to one column. Pass a multi-column range and the entire table spills out, sorted while keeping each row intact.

Suppose A2:C20 holds Name, Region, and Sales. To order the whole table, use the range as the array.

When you sort a table, the sort_index tells SORT which column to order by. It counts columns inside the array, not on the worksheet.

For the Name/Region/Sales table in A2:C20, sort by Sales (the 3rd column) from highest to lowest. The whole row travels with its sales value.

If your data runs across in a row rather than down a column, set the fourth argument by_col to TRUE.

SORT then reorders the columns left to right instead of the rows top to bottom. This is rare but handy for horizontally laid out data.

Because SORT produces many results from one cell, the cells below and beside it must be empty. If anything is in the way, you get a #SPILL! error.

Type the formula in an open area and let the array land cleanly. Clear out any stray values blocking the spill range.

Google Sheets has the same SORT function with the same arguments, so your formulas carry over. Sheets also offers an older SORT range menu and a slightly different legacy behavior, but the formula form matches Excel.

One note: in Sheets you can stack several sort keys by adding extra index/order pairs, like sorting by Region then by Sales.

SORT shines when wrapped around other dynamic arrays. Filter your data first, then sort the result, all in one formula.

Here we keep only East-region rows with FILTER, then sort those by the 3rd column descending. The output is a clean, ordered, filtered list that refreshes itself.

The real payoff of SORT is that it is live. Add a new row to your source range (or use a full-column reference), and the sorted output instantly includes it in the right position.

This makes SORT ideal for reports and dashboards that update as fresh data arrives, with zero manual re-sorting.

You want to sort the table in A2:C20 by the third column from largest to smallest. Which formula is correct?

You learned that SORT orders data with a formula that spills automatically:

• =SORT(array) sorts the first column ascending
• sort_index picks which column to sort by
• sort_order uses 1 for ascending and -1 for descending
• by_col TRUE sorts columns instead of rows

Leave room for the spill, and wrap SORT around FILTER for ordered, filtered, self-updating lists.