Excel TAKE function to extract rows or columns from array

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 :)

TAKE function in Excel

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: Excel TAKE function

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.

TAKE function availability

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.

How to use TAKE function in Excel

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

  1. The array argument can be a range of cells or an array of values returned by another formula.
  2. 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.
  3. 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.
  4. If the rows or columns value is greater than there are rows or columns in the source array, all rows / columns are returned.
  5. 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.

Excel TAKE formula examples

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.

Extract rows from a range or array

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. Extract rows from a range of cells.

Take columns from an array or 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. Take a specified number of columns from a range.

Extract a certain number of rows and columns

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 2nd argument) and two columns (as defined in the 3rd argument). Extract a certain number of rows and columns with one formula.

Get last N rows

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. Get last N rows from a range of cells.

Return last N columns

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: Extract some columns from the end of an array.

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

How to take rows / columns from multiple ranges

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

  1. Combine multiple ranges into one vertically or horizontally using the VSTACK or HSTACK function.
  2. 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.

Stack ranges vertically and take rows or columns

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 3rd argument:

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

The output will look something like this: Stack ranges vertically and take rows or columns.

Stack ranges horizontally and take rows or columns

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) Stack ranges horizontally and take rows or columns.

TAKE function alternative for Excel 2010 - 365

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:

  1. For the 1st argument, supply the original range of values.
  2. The 2nd and 3rd 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.
  3. In the 4th argument, indicate the number of rows to return.
  4. In the 5th 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: TAKE function alternative for Excel 2010 and higher

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

Excel TAKE function not working

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 not supported in your version of Excel

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

Empty array

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

Insufficient number of blank cells to fill with the results

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!

Practice workbook for download

Excel TAKE formula - examples (.xlsx file)

Table of contents

9 comments

  1. Hi sir,

    In the TAKE Formula if I enter the formula =TAKE(myarray,{1;-1}) and hit enter, The excel is returning me a #VALUE! error.

    But if I use the combined formula of =TAKE(SORTBY(myarray,myarray,1),{1;-1}) , Then the correct answer is getting displayed. Please suggest me a solution to get the first and last values of an array in excel using TAKE FORMULA.

    1. Hi! It seems that the TAKE function has some problems when it comes to working with named ranges, if you want to extract multiple values at once. So you can use another function to return a array of values in the desired state. For example, you can use VSTACK function to return an array of values from a named range.

      =TAKE(VSTACK(myarray),{1;-1})
      or
      =VSTACK(TAKE(myarray, 1), TAKE(myarray, -1))

  2. I Tried to take the last 21 cells form Row A:A with 50 cells has value, so i tried this:

    =Take(A:A,-21)

    however I only get 21 numbers of "0"

    So what did i missing in the formula again, please

    1. Hi! If you specify the entire A:A column, you extract the last 21 values from 1,048,576 cells in this column, not the 50 data cells. Specify the exact range.

      =Take(A1:A50,-21)

  3. I have a list of files, listed as file paths and I just want the file. So when I do Text to Columns, there are varying amounts of columns in each row. So if I follow the -1 rule, it will show "0" if the range changes. How would I go about grabbing the last *populated* cell in a row?

  4. This looks interesting.

    I have an range of measuring data for every second, but the changes in data are so slow that datapoints for every 5 minutes would be enough. meanwhile the large datafile is hogging the system down, so I would like to generate a smaller dataset.

    Can I use the Take function to get every 300th number from a range? or are there better ways to do that?

    thanks

  5. As always, a concise, thorough and easy to understand explanation!
    Makes a formula much easier to understand instead of using INDEX

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)