by Alexander Frolov, updated on

*Meet the new Excel TAKE function that can get a specified number of rows or columns from a range or array in a trice.*

When working with large arrays of data, you may sometimes need to extract a smaller part for closer examination. With the new dynamic array function introduced in Excel 365, it will be a walk in the park for you. Just specify how many rows and columns you want to take and hit the Enter key :)

The Excel TAKE function extracts the specified number of contiguous rows and/or columns from the array or range.

The syntax is as follows:

TAKE(array, rows, [columns])

Where:

**Array** (required) - the source array or range.

**Rows** (optional) - the number of rows to return. A positive value takes rows from the start of the array and a negative value from the end of the array. If omitted, *columns* must be set.

**Columns** (optional) - the number of columns to return. A positive integer takes columns from the start of the array and a negative integer from the end of the array. If omitted, *rows* must be defined.

Here's how the TAKE function looks like:

Tips:

- To return non-adjacent rows from a range, use the CHOOSEROWS function.
- To pull non-adjacent columns, utilize the CHOOSECOLS function.
- To get part of the array by removing a given number of rows or columns, leverage the DROP function.

The TAKE function is only supported in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.

In earlier Excel versions, you can use an OFFSET formula as an alternative solution.

To align expectations and reality when using the TAKE function in your worksheets, take notice of the following things:

- The
*array*argument can be a range of cells or an array of values returned by another formula. - The
*rows*and*columns*arguments can be positive or negative integers. Positive numbers take a subset of data from the start of the array; negative numbers - from the end. - The
*rows*and*columns*arguments are optional, but at least one of them should be set in a formula. The omitted one defaults to the total number of rows or columns in the array. - If the
*rows*or*columns*value is greater than there are rows or columns in the source array, all rows / columns are returned. - TAKE is a dynamic array function. You enter the formula in only one cell and it automatically spills into as many neighboring cells as needed.

Now that you have a general understanding of how the TAKE function works, let's look at some practical examples to illustrate its real value.

To return a given number of contiguous rows from the start of a 2D array or range, supply a positive number for the *rows* argument.

For example, to take the first 4 rows from the range A3:C14, the formula is:

`=TAKE(A3:C14, 4)`

The formula lands in cell E3 and spills into four rows and as many columns as there are in the source range.

To get a certain number of contiguous columns from the start of a 2D array or range, provide a positive number for the *columns* argument.

For example, to pull the first 2 columns from the range A3:C14, the formula is:

`=TAKE(A3:C14, ,2)`

The formula goes to cell E3 and spills into two columns and as many rows as there are in the supplied range.

To retrieve a given number of rows and columns from the beginning of an array, you provide positive numbers for both the *rows* and *columns* arguments.

For example, to take the first 4 rows and 2 columns from our dataset, the formula is:

`=TAKE(A3:C14, 4, 2)`

Entered in E3, the formula fills four rows (as set in the 2^{nd} argument) and two columns (as defined in the 3^{rd} argument).

To pull a certain number of rows from the end of an array, provide a negative number for the *rows* argument. For example:

To take the **last row**, use -1:

`=TAKE(A3:C14, -1)`

To get the **last 3 rows**, supply -3:

`=TAKE(A3:C14, -3)`

In the screenshot below, you can observe the results.

To extract some columns from the end of an array or range, use a negative number for the *columns* argument. For example:

To get the **last column**, set the 3rd argument to -1:

`=TAKE(A3:C14, , -1)`

To pull the **last 2 columns**, set the 3rd argument to -2:

`=TAKE(A3:C14, , -2)`

And here are the results:

Tip. To take **rows and columns** from the end of an array, provide negative numbers for both the *rows* and *columns* arguments.

In situation when you want to extract some columns or rows from several non-contiguous ranges, it takes two steps to accomplish the task:

- Combine multiple ranges into one vertically or horizontally using the VSTACK or HSTACK function.
- Return the desired number of columns or rows from the combined array.

Depending on the structure of your worksheet, apply one of the following solutions.

Let's say you have 3 separate ranges like shown in the image below. To append each subsequent range to the bottom of the previous one, the formula is:

`=VSTACK(A4:C6, A10:C14, A18:C21)`

Nest it in the *array* argument of TAKE, specify how many *rows* to return, and you will get the result you are looking for:

`=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), 4)`

To return *columns*, type the appropriate number in the 3^{rd} argument:

`=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), ,2)`

The output will look something like this:

In case the data in the source ranges is arranged horizontally in rows, use the HSTACK function to combine them into a single array. For example:

`=HSTACK(B3:D5, G3:H5, K3:L5)`

And then, you place the above formula inside the TAKE function and set the *rows* or *columns* argument, or both, according to your needs.

For example, to get the first 2 rows from the stacked array, the formula is:

`=TAKE(HSTACK(B3:D5, G3:H5, K3:L5), 2)`

And this formula will bring the last 5 columns:

`=TAKE(HSTACK(B3:D5, G3:H5, K3:L5), ,5)`

In Excel 2019 and earlier versions where the TAKE function is not supported, you can use OFFSET as an alternative. Though the OFFSET formula is not as intuitive and straightforward, it does provide a working solution. And here's how you set it up:

- For the 1
^{st}argument, supply the original range of values. - The 2
^{nd}and 3^{rd}arguments or both set to zero or omitted, assuming you are extracting a subset from the beginning of the array. Optionally, you can specify how may rows and columns to offset from the upper-left cell of the array. - In the 4
^{th}argument, indicate the number of rows to return. - In the 5
^{th}argument, define the number of columns to return.

Summing up, the generic formula takes this form:

OFFSET(*array*, , , *rows*, *columns*)

For instance, to extract 6 rows and 2 columns from the start of the range A3:C14, the formula goes as follows:

`=OFFSET(A3:C14, , , 6, 2)`

In all versions except Excel 365 and 2021 that handle arrays natively, this only works as a traditional CSE array formula. There are two ways to enter it:

- Select the range of cells the same size as the expected output (6 rows and 2 columns in our case) and press F2 to enter Edit mode. Type the formula and press Ctrl + Shift + Enter to enter it in all the selected cells at once.
- Enter the formula in any empty cell (E3 in this example) and press Ctrl + Shift + Enter to complete it. After that, drag the formula down and to the right across as many rows and columns as needed.

The result will look similar to this:

Note. Please be aware that OFFSET is a volatile function, and it may slow down your worksheet if used in many cells.

In case a TAKE formula does not work in your Excel or results in an error, it's most likely to be one of the below reasons.

TAKE is a new function and it has limited availability. If your version is other than Excel 365, try the alternative OFFSET formula.

If either the *rows* or *columns* argument is set to 0, a #CALC! error is returned indicating an empty array.

In case there are not enough empty cells for the formula to spill the results into, a #SPILL error occurs. To fix it, just clear the neighboring cells below or/and to the right. For more details, see How to resolve #SPILL! error in Excel.

That's how to use the TAKE function in Excel to extract rows or columns from a range of cells. I thank you for reading and hope to see you on our blog next week!

Excel TAKE formula - examples (.xlsx file)

Table of contents