TAKE Function

Keeps a number of rows or columns from the start or end of an array.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2024

Returns

A spilled array

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

Overview

TAKE keeps just the slice you want: the first 5 rows, the last 3, the first 2 columns, and so on. It's the opposite of DROP.

Positive numbers take from the top or left; negative numbers take from the bottom or right.

Syntax

=TAKE(array, rows, [columns])

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to take from.
rowsYesRows to keep. Positive = from the top, negative = from the bottom.
columnsOptionalColumns to keep. Positive = from the left, negative = from the right.

Examples

Example 1 — Top 3 performers

Sort by sales, then keep the first three rows.

D2fx=TAKE(SORT(A2:B6, 2, -1), 3)
ABCDE
1RepSalesRepSales
2Ana400Cara500
3Ben350Ana400
4Cara500Ben350
5Dev275
=TAKE(SORT(A2:B6, 2, -1), 3) Explain

Example 2 — The latest entries

Keep the last 5 rows of a growing log.

=TAKE(TRIMRANGE(A2:C1000), -5) Explain
Leave rows empty to take only columns: =TAKE(A2:F20, , 2) keeps the first two columns.

Common mistakes

  • #CALC! errors. rows or columns is 0, giving an empty result.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.