Can I use TAKE to extract the last few rows of a dynamically growing list?

Yes. Use a negative rows value like -5 to always grab the last 5 rows, regardless of how many rows the list contains. This is ideal for monitoring recent entries or the most recent transactions.

Does TAKE include the header row in its count?

Yes. If you want the header plus 3 data rows, use TAKE(array, 4). If you want only data rows without headers, exclude the header range from your input by starting at the first data row.

What's the difference between TAKE and DROP?

TAKE returns a specified number of rows or columns; DROP removes a specified number from the beginning or end. Use DROP to skip rows, and TAKE to grab them. You can combine both to extract a 'middle' slice.

Can TAKE extract non-contiguous columns like Ingredient and Expiry Date but skip Supplier and Unit?

No, TAKE only works with contiguous (adjacent) columns. For non-contiguous selections, use CHOOSECOLS, which accepts a list of specific column numbers to extract in any order you want.

TAKE function

TAKE extracts a contiguous subset of rows and columns from an array, starting from the beginning or end; use it when you need the first N items or last M items.

=TAKE(array, rows, [columns])

Generate a TAKE formula

Describe what you need. The generator will reach for TAKE where TAKE is the right tool, and tell you when it is not.

Arguments

How TAKE reads its arguments

Argument	Required	Description
array	Required	The source array or range to extract from; can include or exclude headers. Must be a 2D array or rectangular range.
rows	Required	Number of rows to take. Positive values take from the beginning; negative values take from the end. Required, and cannot be 0.
columns	Optional	Number of columns to take from the beginning (positive) or end (negative). Optional; if omitted, all columns are returned.

Returns

An array containing the specified number of rows and columns from the input array.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Preview the first few ingredients in stock

Ingredient	Supplier	Unit	Qty	Expiry Date
Olive Oil	Giuseppe Imports	L	12	2026-12-15
Basil	Fresh Farms Co	kg	5	2026-09-22
Mozzarella	Dairy Plus	kg	8	2026-10-01
Tomatoes	Fresh Farms Co	kg	20	2026-09-20
Garlic	Giuseppe Imports	kg	3	2026-11-30

=TAKE(A1:E6, 4)

Result: IngredientSupplierUnitQtyExpiry DateOlive OilGiuseppe ImportsL122026-12-15BasilFresh Farms Cokg52026-09-22MozzarellaDairy Pluskg82026-10-01

TAKE extracts 4 rows (header plus first 3 items) from the 6-row range. Since no column count is specified, all 5 columns are included. This creates a quick preview without scrolling through the entire inventory.

2. Extract ingredient names and suppliers only

Ingredient	Supplier	Unit	Qty	Expiry Date
Olive Oil	Giuseppe Imports	L	12	2026-12-15
Basil	Fresh Farms Co	kg	5	2026-09-22
Mozzarella	Dairy Plus	kg	8	2026-10-01
Tomatoes	Fresh Farms Co	kg	20	2026-09-20
Garlic	Giuseppe Imports	kg	3	2026-11-30

=TAKE(A1:E6, 4, 2)

Result: IngredientSupplierOlive OilGiuseppe ImportsBasilFresh Farms CoMozzarellaDairy Plus

By specifying both rows (4) and columns (2), TAKE returns only the first two columns (Ingredient and Supplier) for the header plus first 3 items. This is useful for creating a focused report showing only relevant fields without clutter.

3. Check the most recent items added to inventory

Olive Oil	Giuseppe Imports	L	12	2026-12-15
Basil	Fresh Farms Co	kg	5	2026-09-22
Mozzarella	Dairy Plus	kg	8	2026-10-01
Tomatoes	Fresh Farms Co	kg	20	2026-09-20
Garlic	Giuseppe Imports	kg	3	2026-11-30

=TAKE(A2:E6, -2)

Result: TomatoesFresh Farms Cokg202026-09-20GarlicGiuseppe Importskg32026-11-30

The negative value -2 tells TAKE to count from the end and return the last 2 rows. This works on the data-only range A2:E6 (excluding headers), making it easy to spot the most recent inventory additions.

Common errors

Which TAKE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The rows argument is 0, which is not a valid count for TAKE.	Change rows to a non-zero integer, such as 1, 3, or -1 to return at least one row.
#VALUE!	The rows or columns argument is text or a non-integer value (for example, "three" or 2.5), which TAKE cannot interpret.	Ensure both rows and columns are integers; use ROUNDDOWN if deriving the count from a formula.
#REF!	The array range is invalid or points to deleted cells, such as a formula referencing a removed column.	Verify the array range exists and is valid; update the reference if columns or rows were added or removed.

Tips and when to use something else

Use positive rows to grab the first N rows, or negative rows to grab the last N rows—perfect for 'top' and 'bottom' queries on dynamic lists.
TAKE includes headers in its count; if your data has headers in row 1, use TAKE(A1:E6, 4) to get the header plus 3 data rows.
For non-contiguous columns (such as Ingredient and Qty but not Supplier), use CHOOSECOLS instead; TAKE only works with adjacent columns.
TAKE is simpler than INDEX or OFFSET for extracting rectangular slices, but unlike XLOOKUP it cannot search—it always extracts from a position, not based on a lookup value.

Related functions

DROPDROP removes specified rows or columns from an array, returning the rest when you need to exclude headers, skip entries, or remove unwanted fields.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.CHOOSEROWSCHOOSEROWS returns specific rows from a given array based on one or more row numbers, spilling the selected rows into adjacent cells.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

Can I use TAKE to extract the last few rows of a dynamically growing list?: Yes. Use a negative rows value like -5 to always grab the last 5 rows, regardless of how many rows the list contains. This is ideal for monitoring recent entries or the most recent transactions.
Does TAKE include the header row in its count?: Yes. If you want the header plus 3 data rows, use TAKE(array, 4). If you want only data rows without headers, exclude the header range from your input by starting at the first data row.
What's the difference between TAKE and DROP?: TAKE returns a specified number of rows or columns; DROP removes a specified number from the beginning or end. Use DROP to skip rows, and TAKE to grab them. You can combine both to extract a 'middle' slice.
Can TAKE extract non-contiguous columns like Ingredient and Expiry Date but skip Supplier and Unit?: No, TAKE only works with contiguous (adjacent) columns. For non-contiguous selections, use CHOOSECOLS, which accepts a list of specific column numbers to extract in any order you want.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17