Excel SORT Function — Sort Without Touching Your Data (SORT vs SORTBY)

TL;DR — The ribbon's Sort button rearranges your real rows; the SORT function returns a sorted copy that re-sorts itself whenever the source changes. Syntax: =SORT(array, [sort_index], [sort_order], [by_col]). The rule that bites everyone: sort_index is a column number, not a letter — SORT(data, 2, -1) sorts by the 2nd column, descending; out of range gives #VALUE!. To sort by a key that isn't in your output, don't force it into the index — use SORTBY. Excel 365 and 2021+.

=SORT(Sales)                       ' sorted copy, by first column, ascending
=SORT(Sales, 2, -1)                ' by 2nd column, descending
=SORTBY(Names, Scores, -1)         ' names ordered by a score column not shown

Sorting used to mean selecting the range, clicking Sort, and permanently rewriting the order of your rows — fine once, painful when the data keeps arriving. The SORT function flips that: it leaves the source untouched and hands you a live, ordered view that never goes stale.

What you'll learn

• Why SORT is a live copy and the ribbon button is a one-time rewrite
• The sort_index rule: it's a column number, and what #VALUE! means
• Sorting by several columns at once
• When SORTBY is the right tool instead of SORT
• The live =SORT(UNIQUE(FILTER(...))) pattern for dashboards and dropdowns

The mental model: a sorted copy, not a rewrite

The Sort button on the ribbon is destructive and manual: it physically reorders the rows you selected, and it has no memory — add a row tomorrow and you sort again. The SORT function is a mirror with an order: it reads the source, returns a sorted copy into its own spill range, and re-sorts the instant the source changes. Your original data stays in whatever order it arrived, which matters when that order is itself meaningful (entry sequence, an ID column) or when the source is a table you keep appending to.

' Source A2:C100 stays exactly as entered; this spills a sorted view elsewhere:
=SORT(A2:C100, 3, -1)        ' sorted by column 3 (the 3rd column), highest first

The rule that bites everyone: sort_index is a number

sort_index tells SORT which column to order by — and it's a position number within the array, counting from 1, not the worksheet column letter. If your array is A2:C100, then 1 is column A, 2 is B, 3 is C. Ask for a column that doesn't exist in the array and you get #VALUE!:

=SORT(A2:C100, 2)        ' by the 2nd column of the array (B), ascending
=SORT(A2:C100, 4)        ' -> #VALUE!  the array only has 3 columns

sort_order is 1 for ascending (the default) and -1 for descending — not the words "asc"/"desc". Keep those two facts straight and 90% of SORT errors disappear. The fourth argument, by_col, flips the whole thing to sort columns left-to-right instead of rows top-to-bottom; you'll rarely need it.

Sorting by several columns at once

Real sorts are usually multi-key: region first, then amount within each region. Pass arrays in braces for both the index and the order:

' Sort by column 1 ascending, then column 3 descending within ties:
=SORT(Sales, {1, 3}, {1, -1})

The two {...} lists line up position by position: column 1 gets order 1 (ascending), column 3 gets order −1 (descending). That's a primary and a secondary sort key in one formula — the kind of thing the ribbon dialog makes you click through three drop-downs to set up.

When to reach for SORTBY instead

Here's the distinction that decides which function to use. SORT can only order by a column that's inside the array it returns. SORTBY separates the thing you display from the thing you sort on — so you can order a list of names by a score column you don't want to show:

' SORT can't do this cleanly — the sort key (Scores) isn't in the output (Names):
=SORTBY(Names, Scores, -1)               ' names, ranked by score, score hidden

' Sort by two external keys:
=SORTBY(Names, Region, 1, Hire_Date, 1)  ' by region, then hire date

The judgment call: if your sort key is a column you're already returning, SORT with an index is simplest. The moment you want to sort by something that isn't in the output — or by an expression like SORTBY(data, LEFT(Code, 2)) — stop wrestling with SORT's column index and switch to SORTBY. Forcing an external key into SORT is the single most common reason people decide "SORT can't do what I need," when really they wanted SORTBY all along.

The live pattern: SORT around UNIQUE and FILTER

SORT is the outermost layer of the most useful formula in modern Excel. Combine it with UNIQUE and FILTER and one expression produces a clean, ordered, self-maintaining list:

=SORT(UNIQUE(FILTER(Customer, Region = "West")))

FILTER selects, UNIQUE dedups, SORT orders — and it all re-runs when a sale lands. This is the standard source for a dynamic dropdown (point Data Validation at the spill with #) or a dashboard list that maintains itself. No manual re-sorting, no refresh, no macro.

How ExcelMaster helps

Most sorting requests are really "show me this, ranked, and keep it ranked" — top accounts by balance, open tickets oldest-first, a leaderboard. ExcelMaster writes the right formula — SORT with the correct numeric index and order, or SORTBY when the key sits outside the output — from a plain-English description, and drops the live view on the tab you name. You describe the ranking; it gets the index, the -1, and the multi-key braces right.

Frequently asked questions

What is the difference between SORT and the Sort button?

The SORT function returns a sorted copy into a new range and re-sorts automatically when the source changes, leaving your original data untouched. The ribbon Sort button permanently rewrites the order of the rows you selected and must be re-run after every data change.

How do I sort by multiple columns with the SORT function?

Pass arrays in braces for the index and order: =SORT(data, {1, 2}, {1, -1}) sorts by the first column ascending, then the second column descending within ties.

What is the difference between SORT and SORTBY?

SORT orders an array by one of its own columns (referenced by number). SORTBY orders an array by one or more separate arrays or expressions, so you can sort by a key that isn't part of the displayed result.

Why does SORT return #VALUE!?

Usually because sort_index points to a column that doesn't exist in the array — it counts positions within the array starting at 1, not worksheet letters. Check that the index is within the number of columns you passed.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-16.

Related guides: Excel FILTER · Excel UNIQUE · XLOOKUP in Excel