Trim Arrays with TAKE & DROP

Excel Formulas › Dynamic Arrays

365TAKE

Grab the first or last few rows/columns of a range — or chop them off — with TAKE and DROP. Perfect for “top 5,” “skip the header,” or “last column.”

Quick formula: keep the first 5 rows, or drop the header row:
=TAKE(A2:C100, 5) // first 5 rows =DROP(A1:C100, 1) // drop the header row
A positive count takes/drops from the start; a negative count works from the end.

Functions used (tap for the full reference guide):

TAKEDROP

The example

Take the first 2 rows of a range.

AB
1NameScore
2Ann95
3Bo88

The formula

Take or drop rows/columns:

=TAKE(range, 5) // first 5 rows =TAKE(range, -3) // last 3 rows =DROP(range, 1) // skip first row

How it works

Count rows from either end:

  1. TAKE(array, rows) keeps that many rows from the start; a negative count keeps them from the end.
  2. A third argument does the same for columns: TAKE(array, , 2) keeps the first two columns.
  3. DROP is the opposite — it removes that many rows/columns. DROP(array, 1) skips a header.
  4. Combine them: TAKE(DROP(data, 1), 5) skips the header, then keeps the first five data rows.

Top-N made easy: sort first, then take — =TAKE(SORT(data, 2, -1), 5) returns the top 5 by the second column. Pre-365, this needed LARGE plus INDEX gymnastics.

Try it: interactive demo

Live demo

Items (one per line); TAKE first N.

Result:

Variations

Last N rows

Negative count:

=TAKE(range, -3)

Skip header

Drop row 1:

=DROP(range, 1)

Top 5 by column

Sort then take:

=TAKE(SORT(data, 2, -1), 5)

Pitfalls & errors

365 only. TAKE/DROP are recent dynamic-array functions.

Rows vs columns argument. The 2nd argument is rows, the 3rd is columns — leave rows blank (, , 2) to act on columns only.

Spill space. Results spill; blocked cells give #SPILL!.

Practice workbook

📊
Download the free Trim Arrays with TAKE & DROP practice workbook
TAKE/DROP examples (formula text + result) with last-N, skip-header, and top-5 variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get the first or last few rows in Excel?
Use =TAKE(range, 5) for the first 5 rows or =TAKE(range, -3) for the last 3. TAKE keeps rows; DROP removes them. Requires Excel 365.
How do I skip a header row with a formula?
Use =DROP(range, 1) to drop the first row. Combine with TAKE: =TAKE(DROP(data,1), 5).
How do I get the top 5 rows by a column?
Sort then take: =TAKE(SORT(data, columnIndex, -1), 5).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sort by multiple columns · Sum top N · CHOOSEROWS & CHOOSECOLS

Function references: TAKE · DROP