The Excel SORTBY function sorts a range by one or more other ranges — the sort keys never have to appear in the result. Show names ordered by salary without showing the salary, sort by as many tie-breaker columns as you like, or impose a completely custom order. It’s the flexible sibling of SORT.
by_array/sort_order pairs for tie-breakers.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to return, sorted. |
by_array1 | Required | The range to sort by. Must have the same number of rows (or columns) as array — it does not have to be part of it. |
sort_order1 | Optional | 1 = ascending (default) · -1 = descending. |
by_array2, sort_order2, ... | Optional | Additional key/order pairs, applied as tie-breakers in order. |
Available in: Excel for Microsoft 365, Excel 2021+, and Excel for the web. Older versions show #NAME?. The result spills like every dynamic array.
Sort by a column you don’t show
The staff table sits in A2:C7. Column E lists just the names, ordered by salary — the key column stays out of the result:
| A | B | C | E | ||
|---|---|---|---|---|---|
| 1 | Name | Dept | Salary | By salary ↓ | |
| 2 | James Chen | Sales | $64,000 | Priya Patel | |
| 3 | Maria Lopez | Ops | $71,500 | Sam Tanaka | |
| 4 | Devon Smith | Sales | $58,200 | Maria Lopez | |
| 5 | Priya Patel | IT | $83,000 | Alex Rivera | |
| 6 | Alex Rivera | Ops | $67,400 | James Chen | |
| 7 | Sam Tanaka | IT | $76,900 | Devon Smith |
Return the whole table instead by widening the first argument — the keys still come from wherever you point:
Try it: interactive SORTBY demo
Sort the staff names by a key column of your choice — notice the key itself never appears in the output.
Multiple keys, custom orders, even a shuffle
Chain key/order pairs for tie-breakers — department A→Z, then salary high-to-low inside each department:
The key can be a calculated array, which unlocks custom sort orders. Sort statuses in the business order High → Medium → Low instead of alphabetically:
Or sort by computed values like text length — or by random numbers for a fair shuffle:
Errors & common pitfalls
#VALUE! — mismatched sizes. Every by_array must have exactly the same number of rows (or columns) as array. Sorting A2:A10 by C2:C9 fails — count your rows. Invalid sort_order values (anything but 1 or -1) also return #VALUE!.
#SPILL! — the landing zone isn’t empty. The result needs as many blank cells as array occupies. Clear whatever blocks the spill range.
#NAME? — older Excel. SORTBY requires Excel 2021+ or Microsoft 365. Excel 2019 and earlier don’t recognize the name.
Pitfall: keys come in pairs. After by_array1, arguments alternate key, order, key, order. Skipping an order in the middle shifts everything and produces baffling results or errors.
Pitfall: headers inside the ranges. Include row 1 in either array or a by_array (but not both) and the sizes no longer match; include it in both and your header row gets sorted into the data. Start every range at the first data row.
Practice workbook
Frequently asked questions
When should I use SORTBY instead of SORT?
How do I sort by multiple columns with SORTBY?
=SORTBY(A2:C20, B2:B20, 1, C2:C20, -1) sorts by column B ascending, then column C descending as the tie-breaker. You can chain as many pairs as you need.Can I sort in a custom order, like High/Medium/Low?
=SORTBY(A2:B20, MATCH(B2:B20, {"High","Medium","Low"}, 0)). MATCH converts each label to 1, 2, or 3 and SORTBY sorts by those numbers.How do I randomly shuffle a list?
=SORTBY(A2:A50, RANDARRAY(49)) — RANDARRAY assigns each row a random number and SORTBY orders by it. Press F9 for a new shuffle. Make sure RANDARRAY's count matches the list's row count.Why does SORTBY return #VALUE!?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class