How to sort a range with a formula

Two functions do this: SORT and SORTBY. In Grid by Zaweba, SORT returns a sorted copy of a range and SORTBY sorts one range by the values in another. The result spills into the cells beside the formula and re-sorts on its own whenever the source data changes, so the original stays exactly as you typed it.

Why sort with a formula at all

The Sort buttons and the Sort dialog rearrange the data itself. That's fine for a list you own, but often the order of the source matters: it's the order things happened, or it's an export you'll replace next week. A formula leaves the source alone and gives you a second view that is always in order. A league table, a top sellers list or a rota sorted by name are all jobs for a formula.

Using SORT

Click an empty cell with room around it, type =SORT( and select the range. With nothing else, SORT orders the rows by the first column, smallest to largest or A to Z. The optional arguments let you choose which column to sort by, given as a number counting from the left of the range, and whether to sort in ascending or descending order.

So a table of names and scores sorted by the second column, highest first, gives you a ranking. Add a new score to the source and the ranking reorders by itself.

The result spills, and Grid shows its extent with a blue spill outline. If a cell in that area already holds something, you'll see #SPILL! until you clear it.

Using SORTBY

SORTBY takes the range to return and then one or more pairs of a sort range and an order. The sort range doesn't have to be part of what you return. You can list just names, ordered by a column of dates you don't show, or sort by one column and then another, such as department and then surname.

Because the sort keys are ranges rather than column numbers, SORTBY is often easier to read later. You can see exactly what the list is ordered by.

Combining with other functions

Sorting often comes after filtering. Put FILTER inside SORT and you get only the rows that meet a condition, already in order, for example this month's orders sorted by value. Wrap UNIQUE in SORT to get an alphabetical list of distinct values. TAKE on a sorted result gives you the top few rows, which is a neat way to make a top five.

To use the sorted result in another formula, refer to it with the spill reference. If SORT sits in H2, H2# means the whole sorted block, however long it grows.

Limits of a spilled sort

A spilled result is values worked out by the formula, so you can't type into the middle of it. If you need to annotate a sorted list, put your notes in a column beside it, or copy the result and paste values only to make a fixed copy.

Mixed data sorts in a fixed order, and text that looks like numbers sorts as text, so 10 can land before 9. Make sure numbers are stored as numbers before relying on the order.

If you only need to reorder data once, the Sort dialog is still the quickest route, with multi-level sorting and custom lists. For anything that should stay in order as the data changes, SORT and SORTBY in Grid by Zaweba do it without ever touching the original.