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
TAKE aani SORT chi order chukli tar "top 3" aivaji "pahile 3 rows" yetat – khup students aadhi TAKE aani mag SORT lavtat. Aadhi SORT, mag TAKE. Formula aatun baher vacha: 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!