CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel: Slice Data Faster

Coding Liquids blog cover featuring Sagnik Bhattacharya for CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel, with sliced array and report-grid visuals.
Coding Liquids blog cover featuring Sagnik Bhattacharya for CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel, with sliced array and report-grid visuals.

CHOOSECOLS and CHOOSEROWS pick specific columns or rows by position from an array or spill range, while TAKE and DROP keep or remove the first or last rows or columns. Together they trim a dynamic-array result where it lands, with no helper columns or manual copying needed.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Instead of copying data, hiding columns, or building helper ranges, you can trim the output with formulas and keep the model far cleaner.

How do CHOOSECOLS, CHOOSEROWS, TAKE and DROP work?

CHOOSECOLS and CHOOSEROWS return the columns or rows you list, in any order, while TAKE and DROP return or remove a block from the start or end of an array.

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.

When should you use CHOOSECOLS versus TAKE and DROP?

Use CHOOSECOLS or CHOOSEROWS to pick particular columns or rows, and TAKE or DROP to trim an edge, such as the first ten rows or a header.

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 use CHOOSECOLS, TAKE and DROP instead of helper columns?

CHOOSECOLS, CHOOSEROWS, TAKE and DROP make modern Excel models easier to maintain, because they shape a spill result exactly where it lands rather than in helper columns. Once a source formula returns too much, you no longer need a second manual step.

Which functions pair well with CHOOSECOLS, TAKE and DROP?

CHOOSECOLS, CHOOSEROWS, TAKE and DROP pair well with GROUPBY, PIVOTBY, FILTER, UNIQUE or SORT: run one of those first, then slice its spill result. They are less about finding data and more about presenting it cleanly.

If you want to build dynamic-array reports with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

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.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

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.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Frequently asked questions

What do these four functions do?

CHOOSECOLS and CHOOSEROWS pick specific columns or rows by position from an array, while TAKE and DROP keep or remove the first or last rows or columns. In short, the first pair is selective picking and the second pair is positional trimming, and both let you shape a spill result with a single formula.

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. The first pair picks named parts of a result; the second trims off the top, bottom, left or right based on where the useful data sits.

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. That means fewer helper columns, less copying and a cleaner, easier-to-maintain model, especially on report tabs and dashboards built from modern arrays.

What do they pair well with?

GROUPBY, PIVOTBY, FILTER, UNIQUE and SORT. They are finishing tools: less about finding data and more about presenting the right slice cleanly. Run the source formula first, then wrap it in CHOOSECOLS, TAKE or DROP to keep only the columns or rows that a report or dashboard needs.

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. For example, listing column 4 and then column 1 returns them in that order, and -1 returns the last column of the array.

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. If the upstream layout might change, a condition-based FILTER is the safer choice, because positional slicing can break when the layout shifts.

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.