9.3 SORT and SORTBY
SORT by a column with example — in 30 seconds
What: SORT returns a sorted copy of a range. The original data stays as it is.
Why: A "biggest orders first" list on a report sheet stays sorted even after new rows arrive.
Tiny example: =SORT(tblMini, 7, -1) sorts by the 7th column (Amount), biggest first. ₹1,299 comes to the top.
SORT by a column with example — 30 सेकंदात
काय: SORT range ची sort केलेली copy देतो. मूळ data जसा आहे तसाच राहतो.
का: Report sheet वरची "सगळ्यात मोठ्या orders आधी" list नव्या rows आल्या तरी sort राहते.
छोटं उदाहरण: =SORT(tblMini, 7, -1) 7व्या column (Amount) नुसार, मोठे आधी sort करतो. ₹1,299 सगळ्यात वर येतो.
SORT by a column with example — 30 सेकंड में
क्या: SORT range की sort की हुई copy देता है. असली data जैसा है वैसा ही रहता है.
क्यों: Report sheet की "सबसे बड़े orders पहले" list नई rows आने पर भी sort रहती है.
छोटा उदाहरण: =SORT(tblMini, 7, -1) 7वें column (Amount) से, बड़े पहले sort करता है. ₹1,299 सबसे ऊपर आता है.
First look at the data (before → after):
Expected result — Spilled result (selected columns)
| Order ID | City | Amount |
|---|---|---|
| BLK-1007 | Solapur | 1299 |
| BLK-1002 | Nashik | 270 |
| BLK-1004 | Nagpur | 240 |
| AMN-1005 | Kolhapur | 232 |
| BLK-1009 | Pune | 220 |
| AMN-1008 | Sambhaji Nagar | 195 |
| BLK-1006 | Pune | 150 |
| AMN-1003 | Pune | 110 |
| BLK-1010 | Nashik | 90 |
| BLK-1001 | Pune | 64 |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on sort and sortby.
Before table and formula as text
Before — tblMini in entry order
| Order ID | City | Amount |
|---|---|---|
| BLK-1001 | Pune | 64 |
| BLK-1002 | Nashik | 270 |
| AMN-1003 | Pune | 110 |
| BLK-1004 | Nagpur | 240 |
| AMN-1005 | Kolhapur | 232 |
| BLK-1006 | Pune | 150 |
| BLK-1007 | Solapur | 1299 |
| AMN-1008 | Sambhaji Nagar | 195 |
| BLK-1009 | Pune | 220 |
| BLK-1010 | Nashik | 90 |
About this data: Demo table only — not real customer or company data. Built to practise sort and sortby.
Formula / click path
K2: =SORT(tblMini, 7, -1)
7 = Amount is the 7th column · -1 = descending
Do it step by step:
- Click an empty cell such as K2.
- Type
=SORT(tblMini, 7, -1). - Press Enter. All ten rows spill, biggest amount first.
- For City A to Z, then Amount high to low, use
=SORTBY(tblMini, tblMini[City], 1, tblMini[Amount], -1). - Check the top row is BLK-1007.
स्टेप बाय स्टेप करून बघा:
- K2 सारख्या रिकाम्या cell वर click करा.
=SORT(tblMini, 7, -1)type करा.- Enter दाबा. दहा rows spill होतात, मोठा amount आधी.
- City A ते Z आणि मग Amount मोठ्या ते लहान साठी
=SORTBY(tblMini, tblMini[City], 1, tblMini[Amount], -1)वापरा. - वरची row BLK-1007 आहे का ते check करा.
स्टेप बाय स्टेप करके देखें:
- K2 जैसी खाली cell पर click करें.
=SORT(tblMini, 7, -1)type करें.- Enter दबाएँ. दसों rows spill होती हैं, बड़ा amount पहले.
- City A से Z और फिर Amount बड़े से छोटे के लिए
=SORTBY(tblMini, tblMini[City], 1, tblMini[Amount], -1)इस्तेमाल करें. - Check करें कि ऊपर की row BLK-1007 है.
=SORT(array, [sort_index], [sort_order], [by_col])– sort by a column number inside the array; order 1 = ascending, -1 = descending.=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)– sort by any array, even one not shown.
| Need | Formula |
|---|---|
| Orders by Amount, largest first | =SORT(tblMini, 7, -1) |
| By City A–Z, then Amount high–low | =SORTBY(tblMini, tblMini[City], 1, tblMini[Amount], -1) |
| Order IDs sorted by delivery time (show only IDs) | =SORTBY(tblMini[Order ID], tblMini[Mins], 1) |
| Pune orders sorted | =SORT(FILTER(tblMini, tblMini[City]="Pune"), 7, -1) |
About this data: Made-up rows so you can copy the pattern for sort and sortby. Currencies may use ₹, £, € or $.
Worked example. =SORTBY(tblMini[Order ID], tblMini[Mins], 1) starts with BLK-1006 (8 mins), BLK-1001 (9), AMN-1008 (10)…
Ravindra Bagale's Tip
In SORT, sort_index is the column number within the array, not on the sheet. If you apply SORT to 3 FILTERed columns, Amount will be column 3, not 7 – many students get #VALUE! here. If in doubt, use SORTBY – it sorts by the column itself.
Ravindra Bagale's Tip – मराठी
SORT मध्ये sort_index हा array मधला column number आहे, sheet चा नाही. FILTER केलेल्या 3 columns वर SORT लावला तर Amount column 3 असेल, 7 नाही – बरेच students इथे #VALUE! मिळवतात. शंका असेल तर SORTBY वापरा – column नावाने sort होतं.
Ravindra Bagale's Tip – हिंदी
SORT में sort_index array के अंदर का column number है, sheet का नहीं. FILTER किए 3 columns पर SORT लगाया तो Amount column 3 होगा, 7 नहीं – बहुत से students यहाँ #VALUE! पाते हैं. शक हो तो SORTBY इस्तेमाल करो – उसमें column के नाम से sort होता है.
Practice task
Show all orders sorted by Platform then Amount (high to low). Show Order IDs sorted by delivery time without displaying the time column.
Loading practice tasks...
- Courses A to Z (spill) (Easy): Sort A2:A7.
- Students low to high (spill) (Easy): Sort C2:C7.
- Students high to low (spill) (Easy): Sort C2:C7 big to small.
- Courses Z to A (spill) (Easy): Sort A2:A7 Z to A.
- Table by students (spill) (Medium): Sort A2:E7 by column 3 (Students), big to small.
- Table by rating (spill) (Medium): Sort A2:E7 by column 4, big to small.
- Cheapest first (spill) (Medium): Sort A2:E7 by column 5, small to big.
- Ratings best first (spill) (Medium): Sort D2:D7 big to small.
- Udemy courses, most students first (spill) (Hard): Show only Udemy rows of A2:E7, sorted by column 3 (Students), big to small.
- Sorted Udemy courses (spill) (Hard): Show Udemy course names sorted A to Z.
- Platforms A to Z, no repeats (spill) (Hard): Sorted list of different platforms.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
