Excel SORTBY Function
Chala mitrano — today SORTBY. Sort this block using that column’s keys — even if keys are outside.
चला मित्रांनो — आज SORTBY. हे ब्लॉक त्या keys ने क्रम — keys बाहेर असल्या तरी.
चलो दोस्तों — आज SORTBY. यह ब्लॉक उन keys से क्रम — keys बाहर हों तो भी.
Purpose
Sort a range by the values in another range or array.
Return value
A spilled array sorted by the by_array.
Syntax
Arguments
| Argument | Required? | Description |
|---|---|---|
array | Required | Array to return sorted. |
by_array1 | Required | Keys that decide order. |
sort_order1 | Optional | 1 asc, −1 desc. |
… | Optional | More by_array/order pairs. |
Version
Microsoft 365 / Excel 2021+ with dynamic arrays. LibreOffice limited; Google Sheets has FILTER/SORT/UNIQUE/SEQUENCE/RANDARRAY cousins.
How to use — Why → What → How
Why: When the sort key is not inside the returned columns, SORTBY shines. Sort packs by weight while showing names — Coimbatore mills and Manchester wholesale. In Indian back offices and foreign sister teams, SORTBY shows up on live dashboards and spill ranges every week.
What: SORTBY orders array according to by_array. Multiple key pairs allowed. Read every argument in the table once before you type; each one changes the answer on purpose.
How — numbered steps:
- Type =SORTBY(A2:A20,B2:B20,-1).
- Keep by_array same height as array.
- Add second keys for tie-breaks.
Ravindra Bagale's Tip
Friends, after SORTBY, find one column or range in a real workbook this week where this lesson saves time.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SORTBY नंतर या आठवड्यात खरी workbook मधील एक संख्या column सुधारा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SORTBY के बाद इस हफ़्ते असली workbook का एक संख्या column सुधारो.
Ravindra Bagale's Tip
Friends, sleep on SORTBY; rebuild example 1 tomorrow without looking.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SORTBY वर झोपा; उद्या उदाहरण 1 न पाहता लिहा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SORTBY पर सोकर कल उदाहरण 1 बिना देखे लिखो.
Ravindra Bagale's Tip
Friends, say the business question in plain words before =SORTBY.
Ravindra Bagale's Tip – मराठी
मित्रांनो, =SORTBY आधी व्यवसाय प्रश्न साध्या शब्दांत सांगा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, =SORTBY से पहले व्यवसाय प्रश्न सादे शब्दों में बोलो.
Ravindra Bagale's Tip
Friends, check a known case with a calculator before you trust a long SORTBY chain.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लांब SORTBY chain वर विश्वास ठेवण्यापूर्वी कॅल्क्युलेटरने एक ज्ञात case तपासा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, लंबी SORTBY chain पर भरोसा करने से पहले कैलकुलेटर से एक जाना हुआ case जाँचो.
Ravindra Bagale's Tip
Friends, after you finish SORTBY, open yesterday’s real workbook and find one place this function would have saved ten minutes.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SORTBY नंतर कालची खरी वर्कबुक उघडा आणि शोधा जिथे हे फंक्शन दहा मिनिटे वाचवू शकले असते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SORTBY के बाद कल की असली वर्कबुक खोलो और ढूँढो जहाँ यह फ़ंक्शन दस मिनट बचा सकता था.
Ravindra Bagale's Tip
Friends, treat SORTBY like a seatbelt on spill ranges — clear #SPILL! blocks first.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SORTBY ला spill ranges वरील seatbelt समजा — आधी #SPILL! साफ करा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SORTBY को spill ranges पर seatbelt समझो — पहले #SPILL! साफ़ करो.
Download practice file: practice-sortby.xlsx (includes sheet About this data, practice sheets, and Answers).
Worked examples
1) Names by marks
Coimbatore.
| Name | Marks |
|---|---|
| Asha | 72 |
| Ben | 90 |
| Cid | 85 |
Result: {Ben;Cid;Asha}
What it means: Names ordered by marks.
2) Asc key
Fees.
| Item | $ |
|---|---|
| Bag | 40 |
| Cup | 20 |
| Lid | 60 |
Result: {Cup;Bag;Lid}
What it means: Cheap first.
3) Manchester £
SKU.
| SKU | £ |
|---|---|
| A | 5 |
| B | 2 |
| C | 8 |
Result: {C;A;B}
What it means: High £ first.
4) Two keys
City then tip.
| Name | City | Tip |
|---|---|---|
| Amy | Pune | 5 |
| Bob | Pune | 12 |
| Cid | Austin | 8 |
Result: {Cid;Bob;Amy}
What it means: Austin then Pune high tip.
5) Full rows
Keep cols.
| Name | Marks |
|---|---|
| Asha | 72 |
| Ben | 90 |
Result: {Ben,90;Asha,72}
What it means: Both columns.
6) #VALUE!
Height mismatch.
| A |
|---|
| x |
| y |
Result: #VALUE!
What it means: by_array height wrong.
Common errors and fixes
Notes and tips
- Revisit the SORTBY errors table after the quiz.
- Build example 1 from memory on a blank sheet.
- Check number vs text if a result looks wrong.
- Teach one tip in Marathi or Hindi this week.
- Keep currency symbols consistent inside one report column (practice mixes ₹ $ £ €).
- Drill: sort three names by a marks column from memory.
- SORTBY vs SORT: keys can live outside the returned spill.
- Fictional sample data only — names and amounts are for learning.
- Print the SORTBY revision card for one week of real worksheets.
- Ask juniors to predict before they press Enter.
- Track your top mistake with this function monthly.
- Sort packs by weight while showing names — Coimbatore mills and Manchester wholesale.
- Interview tip: explain the business question before the formula.
- Classroom drill: invent three brand-new formulas from your city without the page open.
- If a result looks wrong, check blanks vs zeros before rewriting.
- When Excel versions differ, note the LibreOffice / Sheets fallback in a cell comment.
- Prefer clear ranges over whole-column refs on huge sheets if speed matters.
- Document units (₹, kg, days) in the header so totals stay honest.
- Print the revision card for SORTBY for one week of real worksheets.
- When helping a junior, ask them to predict before Enter.
- Share one tip with a classmate in Marathi or Hindi.
- Keep currency symbols consistent inside a single report column.
- Interview tip: explain the business question before the formula.
- If a result looks wrong, check spill range blocks (#SPILL!) before rewriting.
- When Excel versions differ, write a LibreOffice / Sheets fallback under a comment.
In real work
Drill: sort three names by a marks column from memory. Sort packs by weight while showing names — Coimbatore mills and Manchester wholesale. Classroom drill: write three brand-new SORTBY formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose and watch the error; recovering from that break is half of interview readiness. Always verify with a known case before trusting a long chain. Keep a personal notebook of the one mistake you make most often with this function; review it monthly.
How it compares
SORTBY vs SORT: keys can live outside the returned spill.
Interview corner
- vs SORT?
- External keys.
- Mismatch?
- #VALUE!.
- Orders?
- 1 / −1.
Related functions
Practice tasks (10)
- Task 1. Names by marks desc.SORTBY.
- Task 2. Items by $ asc.Cheap.
- Task 3. SKU by £.£.
- Task 4. Two keys.Tie.
- Task 5. Force #VALUE!.Bad.
- Task 6. Interview.Talk.
- Task 7. Teach.Teach.
- Task 8. Full rows.Cols.
- Task 9. Fill.Fill.
- Task 10. Document.Note.
Quick quiz
Answers: Q1=Separate by_array, Q2=Match, Q3=Desc, Q4=External keys OK, Q5=#VALUE! (check after you try).
Revision card
Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.
