Excel TAKE, DROP & CHOOSECOLS: The Complete 2026 Guide to Dynamic Array Slicing

If you have ever written a clunky INDEX/MATCH formula just to grab the first five rows of a dataset, or used OFFSET to skip the first column of a lookup table, you already know the pain. In 2026, Excel’s dynamic array engine has matured to a point where slicing, trimming, and reshaping data is a matter of a single, readable formula. The functions TAKE, DROP, and CHOOSECOLS are the trio that make it happen — and once you understand them, you will wonder how you ever managed without them.

This guide walks you through every practical use case with real examples you can copy directly into your workbook.

What Are TAKE, DROP, and CHOOSECOLS?

These three functions are part of Excel’s modern dynamic array family, fully available in Microsoft 365 as of 2026. They allow you to extract a portion of an array or range without helper columns, without VBA, and without nested complexity.

TAKE: Returns the first or last N rows (or columns) from an array.

DROP: Returns everything except the first or last N rows (or columns).

CHOOSECOLS: Returns specific named columns from an array by position.

TAKE: Extract the First or Last Rows and Columns

Syntax: =TAKE(array, rows, [cols])

The rows argument controls how many rows to return. Use a positive number to take from the top, and a negative number to take from the bottom.

Example 1: Top 5 Sales Records

Suppose you have a table of 200 sales transactions in A1:E200, sorted by revenue descending. To display the top 5 rows in a separate area:

=TAKE(A1:E200, 5)

This spills five rows into the destination range dynamically. If new rows are added to the source table, TAKE automatically updates.

Example 2: Last 3 Months of Data

For a time-series table where the most recent data is at the bottom:

=TAKE(A1:C36, -3)

The negative value tells Excel to count from the end, returning the last 3 rows — ideal for rolling dashboards.

DROP: Remove Rows or Columns from an Array

Syntax: =DROP(array, rows, [cols])

DROP is the complement of TAKE. Instead of returning what you want, it returns everything except what you specify. This is particularly useful when dealing with header rows or footer totals in data exports.

Example 3: Skip a Header Row in a Spilled Array

If another formula returns an array that includes a header row you want to discard:

=DROP(SORT(A1:D50, 2, -1), 1)

Here, SORT first orders the data by column 2 descending, then DROP removes the first row (the header) from the result.

Example 4: Remove the Last Row (Totals Row)

If your data export always adds a totals row at the bottom:

=DROP(A1:B100, -1)

CHOOSECOLS: Pick Exactly the Columns You Need

Syntax: =CHOOSECOLS(array, col_num1, [col_num2], …)

CHOOSECOLS lets you select specific columns from an array by their position number. This is incredibly powerful when combined with data imports or FILTER results that return more columns than you need.

Example 5: Extract Name and Email from a 10-Column Table

Your contact database has 10 columns, but you only need columns 1 (Name) and 4 (Email):

=CHOOSECOLS(A1:J500, 1, 4)

This spills a clean two-column array that automatically updates whenever the source table changes.

Example 6: Reorder Columns

You can also use CHOOSECOLS to change the order of columns in a report:

=CHOOSECOLS(A1:D50, 3, 1, 4, 2)

Combining TAKE, DROP, and CHOOSECOLS for Power Workflows

The real magic happens when you chain these functions together with other dynamic array functions like FILTER, SORT, and UNIQUE.

Example 7: Top 10 Customers, Specific Columns Only

Get the top 10 customers by revenue, showing only Name, Region, and Total Revenue:

=CHOOSECOLS(TAKE(SORT(A2:F200, 6, -1), 10), 1, 3, 6)

Breaking this down: SORT orders all 200 rows by column 6 descending, TAKE grabs the top 10, and CHOOSECOLS extracts just columns 1, 3, and 6.

Example 8: Recent Filtered Results

=DROP(FILTER(A1:E200, C1:C200="UK"), 0, 2)

Here, FILTER returns all UK rows from a 5-column table, and DROP removes the last 2 columns from the result.

Working with CHOOSEROWS: The Row Equivalent

CHOOSEROWS works exactly like CHOOSECOLS but for rows. Use it when you need to extract non-contiguous rows by their index. For example, to build a summary table from rows 1, 5, and 10 of a result set: =CHOOSEROWS(A1:D20, 1, 5, 10). This is ideal for creating comparison reports where specific records need to be displayed side-by-side.

Practical Tips for 2026 Excel Users

Always prefer TAKE/DROP over INDEX combinations: They are more readable and spill correctly into the grid.

Use negative values to work from the end of an array: This is the cleanest way to handle rolling windows of data.

Wrap in IFERROR for graceful handling: If the source array shrinks smaller than your TAKE value, wrap it to avoid errors.

Combine with LET for legibility: Store intermediate arrays in LET variables to make complex chains readable.

CHOOSECOLS accepts negative indices: Use -1 for the last column, -2 for second-to-last, and so on.

Conclusion

TAKE, DROP, and CHOOSECOLS represent a fundamental shift in how Excel handles data extraction. They eliminate the need for fragile, hard-to-read legacy formulas and replace them with clean, self-documenting expressions that work natively with Excel’s dynamic spill engine.

If you are still using OFFSET, INDEX, or helper columns to slice data in 2026, now is the time to upgrade your approach. Start with a simple TAKE formula on your next reporting task and experience firsthand how much cleaner your workbooks can be.

Have questions or a creative use case to share? Drop a comment below — we love hearing how officelearner.net readers are using these functions in the real world.