SORT vs SORTBY: Which One to Use
SORT sorts by a column position inside the range you return. SORTBY sorts by any column you name, even one you leave out of the result. That difference decides which you pick. When the sort key is also a column you show, SORT is shorter. When the key is hidden or comes from another range, SORTBY is the only option.
The SORT Syntax in Plain Terms
SORT takes the array, the column index to sort by, the sort order, and an optional by-column flag. The order is 1 for ascending and -1 for descending. If you skip the column, Excel sorts by the first column. I almost always set the column and order by hand so the result is not a surprise.
Type =SORT( and select your data, like A2:D100 on the Orders sheet. This whole block is returned in the new order, not just the sort column.
Add the column number. To sort by the fourth column, type 4. Count from the left edge of your selected range, not from column A of the sheet.
Type -1 for descending or 1 for ascending. For biggest deals first, use =SORT(A2:D100, 4, -1). The default is ascending, so always set -1 when you want high values on top.
Press Enter and the reordered rows spill from your formula cell. A light blue border marks the live block. Leave those cells empty or you will trigger a #SPILL! error.
The SORTBY Syntax for Hidden Keys
SORTBY is more flexible because the sort key is a separate range. You can sort your returned columns by a column you do not display, or by a column on another sheet. The key range must have the same row count as the array you return.
Write =SORTBY(A2:D100, E2:E100, -1) to show columns A through D sorted by column E, even though E is not in the result. This is perfect when the sort key is a score or a date you want hidden.
If the key lives on Sheet2, write =SORTBY(A2:D100, Sheet2!B2:B100, 1). The two ranges must line up row for row. A mismatch throws #VALUE!.
Add pairs: =SORTBY(A2:D100, C2:C100,1, D2:D100,-1) sorts first by column C ascending, then by column D descending. You can chain as many key pairs as you need for a stable multi-level sort.
I recommend SORTBY over SORT whenever the sort key is not obvious from the returned columns, because a later edit that inserts a column will shift SORT's column index and silently reorder by the wrong field. SORTBY names the key by range, so inserting columns does not break it. A common mistake is using SORT with a hardcoded column number and then wondering why the report flipped after an insert.
Sort Numbers and Dates Descending
Numbers and dates sort the same way: ascending puts small and old first, descending puts large and new first. On the Orders table the Amount column is a dollar value, so -1 puts the highest deals on top. On a dated list, -1 puts the most recent row first, which is what a manager usually wants.
Use =SORT(Orders, 5, -1) where column 5 is Amount. The largest dollar value sits in row one of the spilled block. Every downstream SUM or chart sees the reordered list.
If column 4 holds the order date, write =SORT(Orders, 4, -1). Excel treats dates as numbers, so descending means most recent. This beats clicking the filter arrow every morning.
For a Region or Category column, use 1 for ascending: =SORT(Orders, 3, 1) lists West, then East, then North in alphabetical order. Text sort respects your workbook's locale rules.
Sort by a Computed Column
Sometimes the value you want to sort by does not exist yet. Build it in a helper column, then point SORTBY at that range while returning the original columns. The helper can stay hidden so the sheet looks clean but still drives the order.
In column F write =Orders[Amount]*Orders[Priority] to weight each deal. Higher numbers mean more important rows, and the score updates as Amount or Priority changes.
Use =SORTBY(Orders, F2:F500, -1) to list rows by that score descending. Column F is not in the returned block, but it controls the order, which keeps the report focused on the key columns.
Right-click column F and hide it. The SORTBY formula still reads F2:F500, so the order holds while the reader never sees the math behind it. Avoid deleting the column or the sort loses its key.
Multi-Level Sorts With Two Keys
One key is rarely enough. You might want Region alphabetical, then Amount descending inside each region. SORTBY handles this with paired key arguments, and the order of the pairs decides priority. The first pair is the top-level sort, the second breaks ties.
Write =SORTBY(Orders, Orders[Region],1, Orders[Amount],-1). Rows group by Region A to Z, and within each region the bigger deals sit on top. This mirrors the old Sort dialog's first and second level.
Swap the pairs to =SORTBY(Orders, Orders[Amount],-1, Orders[Region],1) and you get a global top-by-amount list where ties share the same region order. The pair order is the priority order, so think about what should win.
Append another pair, such as Orders[OrderDate],-1, to sort by date when region and amount both tie. Three keys cover almost every real report a team asks for.
Chain SORT With FILTER and UNIQUE
Dynamic array functions nest. Wrap SORT around FILTER to get a sorted sub-list, or around UNIQUE to sort a distinct list. The output of one becomes the input of the next, and the whole chain updates when the source changes.
Write =SORT(FILTER(Orders, Orders[Region]="West", "none"), 5, -1) to show only West deals, already sorted by amount. The inner FILTER returns the rows, the outer SORT reorders them.
Use =SORT(UNIQUE(Orders[Region]), 1, 1) to get each region once, in alphabetical order. UNIQUE drops duplicates, SORT puts them in a clean sequence for a dropdown or a label list.
Build a score in column F with =Orders[Amount]*Orders[Priority], then =SORTBY(Orders, F2:F500, -1). The helper column can stay hidden while the report sorts by it, which keeps the sheet tidy.
A common mistake is sorting a range that includes a total row at the bottom. SORT treats the total as a normal row and shuffles it into the middle. Exclude the total row from your range, or put totals in a separate area. This will break a report that a manager reads top to bottom.
Why SORT and SORTBY Throw Errors
Most failures are shape problems. The key range must match the array row count, the column index must exist, and the spill area must be clear. Learn the three fixes and you will rarely be stuck.
If the key range has more or fewer rows than the array, Excel returns #VALUE!. Make both cover the same rows, for example A2:D100 with E2:E100, not E2:E99.
Clear values, merged cells, or a table in the cells below the formula. SORT needs a clean rectangle equal to the result. Delete the blocker and the spill completes.
On Excel 2019 or earlier, SORT and SORTBY do not exist and you get #NAME?. Upgrade to Excel 365 or 2021, or fall back to the ribbon Sort command for a static result.
SORT Functions vs the Ribbon Sort Button
The ribbon Sort button reorders the real cells and destroys the original order unless you undo. The SORT function leaves the source alone and produces a live copy. For a one-time rearrange, the button is fine. For a report that must survive new data, the function is the safer choice.
If you maintain dashboards, pair these with structured references on an Excel Table so new rows sort automatically. The ribbon sort never does that for you.
Next Steps and a Quick Checklist
Decide SORT versus SORTBY by whether your key is in the returned columns. Set the order argument by hand every time. Chain with FILTER for live sub-lists. Keep the source as a Table so rows added later sort without edits.
Good companions to learn next are FILTER for row selection, UNIQUE for distinct values, and SEQUENCE for building sample data to practice on. Together they cover most live-report needs without a single macro.
