How to Use the TAKE and DROP Functions in Excel

Learn really to precisely extract and manipulate your data, making Excel activity smarter for you.

When moving pinch your dataset successful Microsoft Excel, location are occasions erstwhile you request to analyse only a information of nan data. Thankfully, Excel provides nan TAKE and DROP functions for situations for illustration this.

TAKE and DROP let you to see aliases exclude a information of an array successful your selection. Let's commencement by defining these functions and past research really to usage them successful Excel.

What Is nan TAKE Function successful Excel?

The TAKE usability successful Excel extracts a specified number of rows and columns from an array. The syntax of nan TAKE usability is:

=TAKE(array, rows, [columns])

Here's what each parameter signifies:

• Array: Refers to nan root array from which you wish to extract rows aliases columns.
• Rows: Indicates nan number of rows to extract. A antagonistic number takes rows from nan end.
• Columns: Indicates nan number of columns to extract. A antagonistic number besides takes columns from nan end.

What Is nan DROP Function successful Excel?

On nan different hand, nan DROP usability successful Excel excludes circumstantial rows and/or columns from an array aliases scope of cells. The syntax of nan DROP usability is:

=DROP(array, rows, [columns])

The parameters for nan DROP usability are akin to those successful nan TAKE function:

• Array: Refers to nan root array from which you wish to region rows aliases columns.
• Rows: Indicates nan number of rows to remove. A antagonistic number removes rows from nan end.
• Columns: Indicates nan number of columns to remove. A antagonistic number removes columns from nan end.

The TAKE and DROP functions activity pinch arrays, and they output arrays arsenic well. Since they ever output arrays, there's nary request to people them arsenic array formulas successful Excel. For nan aforesaid reason, you mightiness brushwood the #SPILL correction successful Excel erstwhile utilizing TAKE and DROP.

How to Use nan TAKE Function successful Excel

There are infinite Excel scenarios wherever TAKE mightiness travel successful handy. Let's return a look astatine a fewer examples.

Suppose you person a worksheet containing a database of products and their prices complete nan past fewer months. If you want to extract only nan first 5 rows of nan data, you tin usage nan TAKE usability alternatively of copying nan data.

Here's really you tin do that:

1. Select nan compartment wherever you want to usage nan TAKE function.
2. In nan look bar, participate nan look below. =TAKE(B2:F1, 5)
3. Press Enter.

This look will retrieve nan first 5 rows wrong nan information scope B2:F1 and return nan output.

2. Extract nan First Column of Your Data

Let's opportunity you want to retrieve only nan first file successful nan aforesaid dataset. To do this pinch nan TAKE function, you request to time off nan statement parameter quiet and only specify nan column:

=TAKE(B2:F1,,1)

This look will retrieve nan first file from nan B2:F1 data range. Since we didn't specify immoderate worth for nan row parameter successful our formula, each statement successful nan first file is included successful nan output.

How to Use nan DROP Function successful Excel

You tin usage nan DROP usability to exclude unwanted rows and columns from your data. The syntax for nan formulas is akin to nan TAKE function. Let's spot really nan DROP usability useful successful Excel.

1. Exclude nan Last Ten Rows of Your Data

Suppose you want to create a information position of your worksheet that shows everything isolated from nan past 10 rows. Here is nan look you tin usage to execute this:

=DROP(B2:F21,-10)

This look will exclude nan past 10 rows successful nan B2:F1 information range. This is because we specified -10 as nan statement parameter of data.

2. Exclude nan Last Column of Your Data

You tin usage nan look beneath to trim down nan past file of your information pinch DROP:

=DROP(B2:F21,,-1)

In this example, nan DROP usability will return nan values that autumn wrong nan scope B2:F1 while excluding nan past column.

Combining nan TAKE and DROP Functions With Other Excel Functions

The existent magic of Excel happens erstwhile you harvester different functions together. For instance, if you request to provender a circumstantial portion of an array to a function, you tin harvester it pinch TAKE aliases DROP alternatively of penning retired nan information range.

Suppose you person a array of immoderate items and their specifications and want to output nan first point that costs supra N100. You tin usage TAKE successful operation pinch nan FILTER usability successful Excel to do this. Here's nan look to do that:

=TAKE(FILTER(B3:E8,D3:D8>100), 1,1)

Let's break this look down. The FILTER usability gets each rows pinch prices supra N100 wrong nan range. The look for this is arsenic follows:

=FILTER(B3:E8,D3:D8>100)

Next, nan look feeds nan results from FILTER to nan TAKE function. Specifying some nan row and column arguments arsenic 1 retrieves only nan worth successful nan filtered data's first statement and column.

Take and Drop Data successful Your Sheet

Excel's TAKE and DROP functions connection a powerful measurement to extract and manipulate circumstantial information successful your worksheets. Understanding their syntax and usage allows you to efficiently negociate and analyse your Excel data, particularly erstwhile mixed pinch different functions.