Using the TAKE function to pick out rows or columns of data

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Using the TAKE function to pick out rows or columns of data
Excel's TAKE function allows you to extract a number of rows from the top or bottom of a range, or columns from the left or right. This blog shows you the basics of the TAKE function and how to combine it with other Excel array functions to allow your users to pick useful values from a list.

Posted by Andrew Gould on 29 November 2025

In this blog

Introducing the TAKE Function
A Basic Example
Taking Rows from the Bottom
Taking Columns
Taking Rows from a Sorted List
Getting User Input

Introducing the TAKE Function

The TAKE function allows you to extract rows from the top or bottom, or columns from the left or right of an Excel range. You can see the syntax of the function below:

TAKE(Array, Rows, [Columns])

Here's a description of the function's parameters:

Parameter	Description
Array	The range of cells you want to take rows or columns from.
Rows	The number of rows to take. Use positive numbers to take from the top and negative numbers to take from the bottom.
Columns	An optional parameter to specify the number of columns to take. Use positive numbers to take from the left and negative numbers to take from the right.

A Basic Example

In the example below we're taking the top 3 rows from the list of films in cells A3:E17.

A basic TAKE function in Excel extracting 3 rows from the top of a list

The formula in G3 takes the top 3 rows from the list.

You can see the results of the formula in the diagram below:

The results of a TAKE function in Microsoft Excel

Unsurprisingly, we get the top 3 rows.

Taking Rows from the Bottom

You can use negative numbers to get rows from the bottom of a range, as shown below:

This will take the last 3 rows of the list.

Here are the results of the formula:

Taking the bottom 3 rows from a range in Excel

We return the bottom 3 rows.

Taking Columns

By default, you return all the columns from the range you've specified. You can use the third parameter of the TAKE function to specify how many columns to return, as shown below:

Taking rows and columns from a range using Excel's TAKE funciton

We're asking for the top 3 rows and leftmost 2 columns.

Here are the results of the formula:

The results of a TAKE function in Excel returning rows and columns

We return an array of 3 rows and 2 columns.

Taking Rows from a Sorted List

You can combine the TAKE function with the SORT function to extract rows from a sorted version of a list. In the example below, we're getting the top 3 films sorted in descending order of Run Time (the 3rd column of the list):

Nest the SORT function in the first argument of the TAKE function.

The SORT function in the example above returns a version of the list sorted in descending order of the values in the 3rd column. The TAKE function returns the top 3 rows from this sorted list. Here are the results of the formula:

Returning rows from a sorted list using Excel's TAKE and SORT functions

The top 3 longest films in the list.

Getting User Input

It would be useful to allow the end user to control how many rows to return and what order they should be sorted in. In the example below we've set up some cells to allow the user to specify those values:

Allowing the user to pass values into the TAKE and SORT functions in Excel

We've used data validation to create a drop down list in cell H3.

The formula in cell G6 responds to the choices the user makes in cells H2 and H3. Here's the formula which returns the results:

A formula in Excel using the TAKE SORT and XMATCH functions.

The formula to return the results.

In the example above we're using the XMATCH function to return the number of the column the user has chosen to sort by.

How creative can you be with the TAKE function?

Some other pages relevant to this blog include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS