9.9 CHOOSECOLS, TAKE and DROP
Microsoft 365 / Excel 2024.
| Function | Syntax | Example | Result |
|---|---|---|---|
| CHOOSECOLS | CHOOSECOLS(array, col1, [col2], …) |
=CHOOSECOLS(tblMini, 1, 3, 7) |
Order ID, City, Amount |
| CHOOSEROWS | CHOOSEROWS(array, row1, …) |
=CHOOSEROWS(tblMini, 1, -1) |
First and last row |
| TAKE | TAKE(array, rows, [columns]) |
=TAKE(SORT(tblMini,7,-1), 3) |
Top 3 orders |
| DROP | DROP(array, rows, [columns]) |
=DROP(tblMini, 1) |
All except the first row |
Worked example – Top 3 orders with only three columns:
=TAKE(CHOOSECOLS(SORT(tblMini, 7, -1), 1, 3, 7), 3)
| Order ID | City | Amount |
|---|---|---|
| BLK-1007 | Solapur | 1,299 |
| BLK-1002 | Nashik | 270 |
| BLK-1004 | Nagpur | 240 |
Negative numbers count from the end: =TAKE(tblMini, -2) returns the last two orders.
Ravindra Bagale's Tip
If you get the order of TAKE and SORT wrong, you get "the first 3 rows" instead of the "top 3" – many students apply TAKE first and then SORT. SORT first, then TAKE. Read the formula from the inside out: SORT → CHOOSECOLS → TAKE.
Ravindra Bagale's Tip – मराठी
TAKE आणि SORT ची order चुकली तर "top 3" ऐवजी "पहिल्या 3 rows" येतात – बरेच students आधी TAKE आणि मग SORT लावतात. आधी SORT, मग TAKE. Formula आतून बाहेर वाचा: SORT → CHOOSECOLS → TAKE.
Ravindra Bagale's Tip – हिंदी
TAKE और SORT का order गलत हुआ तो "top 3" की जगह "पहली 3 rows" आती हैं – बहुत से students पहले TAKE और फिर SORT लगाते हैं. पहले SORT, फिर TAKE. Formula को अंदर से बाहर पढ़ो: SORT → CHOOSECOLS → TAKE.
Practice task
Show the 5 slowest deliveries (Order ID, City, Mins). Show the latest 3 orders using TAKE with a negative number on a date-sorted array.
Thodkyaat sangaycha tar (quick recap)
Dynamic array formulas spill hotat – jaga rikami theva aani # ne refer kara; FILTER (AND = *, OR = +, if_empty dya), SORT/SORTBY, UNIQUE, SEQUENCE; LET ne formula vachayla sopa, LAMBDA ne swatahche functions; VSTACK/HSTACK ne jodne, CHOOSECOLS/TAKE/DROP ne kaapne. Sagle Microsoft 365 / navin Excel madhe – file share kartana version lakshat theva. Aata pudhe jaauya – Power Query!