Using TAKE and DROP functions to keep or remove rows or columns from data

Nov 24, 2022 | Excel Hints and Tips

The TAKE and DROP Excel functions allow you to keep or remove a certain number of rows or columns at the start or end of the data.  They are available to Microsoft 365 users only and if they are not currently available they should appear shortly.

TAKE Function

This function keeps a set number of rows or columns from the data.  These could be at the beginning or the end of the list.

Structure

=TAKE(array, rows,[columns])

Array – The array from which to take rows or columns.
Rows – The number of rows to take. A negative value takes from the end of the array.
Columns – The number of columns to take. A negative value takes from the end of the array.  The number of columns is optional.  If left blank all columns are selected.

Example 1

This example keeps the top 3 rows of the data held in A2 to C6.

TAKE Example

Example 2 

In this second example the bottom 3 rows are kept because the rows part of the function is negative. 

TAKE Example 2

DROP Function

The DROP function is similar to TAKE but it removes a certain number of rows or columns from the data.

Structure

=DROP(array, rows,[columns])

Array – The array from which to drop the rows or columns.
Rows – The number of rows to drop. A negative value takes from the end of the array.
Columns – The number of columns to drop. A negative value takes from the end of the array.  The number of columns is optional.  If left blank all columns are kept.

Example 1

This example keeps the drops the top 3 rows of the data held in A2 to C6.

DROP Example 1

Example 2 

In this second example the bottom 3 rows are dropped because the rows part of the function is negative. 

DROP Example 2

Further Reading

If you’ve enjoyed reading how to use TAKE and DROP to keep or remove rows or columns from data in Excel, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.