Excel functions / Dynamic arrays

TAKE function

Keeps the first or last rows or columns of an array.

Syntax

=TAKE(array, rows, [columns])

Arguments

When to use it

TAKE gives back the first rows of a range, or the last ones when the number is negative. A third argument does the same for columns.

On its own it is a quick "top of the list". After SORT it becomes a top-N: sort by sales, largest first, then take 3.

Examples

  1. The first two rows

    ABCDEFG
    1RepRegionSales
    2AnaNorth1200AnaNorth1200
    3BenSouth800BenSouth800
    4ChloeNorth900
    5DevWest400

    In E2: =TAKE(A2:C5, 2) → Ana, North, 1200; Ben, South, 800

    Try this example

  2. The last row

    ABCDEFG
    1RepRegionSales
    2AnaNorth1200DevWest400
    3BenSouth800
    4ChloeNorth900
    5DevWest400

    In E2: =TAKE(A2:C5, -1) → Dev, West, 400

    Try this example

  3. The top two by sales

    ABCDEFG
    1RepRegionSales
    2AnaNorth1200AnaNorth1200
    3BenSouth800ChloeNorth900
    4ChloeNorth900
    5DevWest400

    In E2: =TAKE(SORT(A2:C5, 3, -1), 2) → Ana, North, 1200; Chloe, North, 900

    SORT puts the largest sales first, and TAKE keeps the first two rows of that.

    Try this example

Common mistakes

Common questions

How do I get the top 5 rows in Excel?

Sort, then take: =TAKE(SORT(A2:C100, 3, -1), 5) sorts by the third column, largest first, and returns the first five rows.

TAKE in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try TAKE in the playground

Practise it

Related functions