These four functions are small, but they fix an annoying problem in modern Excel: once you have a useful spill range, how do you quickly keep only the rows or columns you actually need?
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreInstead of copying data, hiding columns, or building helper ranges, you can trim the output with formulas and keep the model far cleaner.
Quick answer
Use CHOOSECOLS and CHOOSEROWS when you want specific positions from a spill result, and use TAKE and DROP when you want the first or last part of a result. They are ideal finishing tools for dynamic-array workflows.
- You already have a spill formula and want to trim it neatly.
- You are building report tabs or dashboards from modern arrays.
- You want fewer helper columns and less manual tidying.
What each function is best at
CHOOSECOLS and CHOOSEROWS are selective. TAKE and DROP are positional. That means you can either pick named parts of a result or trim off the top, bottom, left, or right based on where the useful data sits.
Why these functions matter in real workbooks
They make modern Excel models easier to maintain. Once a source formula returns too much, you no longer need a second manual step. You can shape the result exactly where it lands.
Where they combine well
These functions become especially useful after GROUPBY, PIVOTBY, FILTER, UNIQUE, or SORT. They are less about finding data and more about presenting it cleanly.
Worked example: trimming a report spill range
A report tab spills a wide summary with more columns than stakeholders need. CHOOSECOLS keeps only the customer, region, and revenue fields. TAKE then limits the output to the top ten rows for a compact summary block.
Common mistakes
- Using them to patch messy source data instead of fixing the source table.
- Forgetting that positional slicing can break if the upstream layout changes.
- Overcomplicating a report when a simpler base formula would do.
When to use something else
If the real problem is grouping and summarising, go back to GROUPBY. If the source data itself is awkward, better table structure often helps more than another formula layer.
Frequently asked questions
What do these four functions do?
CHOOSECOLS and CHOOSEROWS pick specific columns or rows by position from an array; TAKE and DROP keep or remove the first or last rows or columns. Selective picking versus positional trimming.
When do I use CHOOSECOLS/CHOOSEROWS versus TAKE/DROP?
Use CHOOSECOLS and CHOOSEROWS when you want specific positions, such as just columns 1 and 4; use TAKE and DROP when you want an edge slice, such as the first 10 rows or dropping a header.
Why are they useful?
They shape a spill result exactly where it lands, so you no longer need a second manual step to trim or reorder the output of a dynamic formula.
What do they pair well with?
GROUPBY, PIVOTBY, FILTER, UNIQUE and SORT. They are finishing tools: less about finding data, more about presenting the right slice cleanly.
Can I select columns in a custom order or from the end?
Yes. CHOOSECOLS takes positions in any order, and negative indices count from the end, so you can reorder columns or grab the last one without knowing the width.
How is DROP different from FILTER?
DROP removes by position, such as dropping the first row, while FILTER removes by condition. Use DROP to trim known edges like headers or totals, and FILTER to keep rows that meet a rule.
Related guides on this site
If you want to keep going without opening dead ends, these are the most useful next reads from this site.