How to use the DROP function

Author: Oscar Cronquist Article last updated on May 05, 2022

DROP function example

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Dynamic array formula in cell B9:

=DROP(B2:D5, 2)

The DROP function is available to Excel 365 users and is in the "Array manipulation" category.

DROP Function Syntax
DROP Function Arguments
DROP Function example
DROP Function example - exclude columns
DROP Function example - exclude columns and rows
DROP Function example - negative values
DROP Function errors
DROP Function - values in random order
DROP Function - multiple source ranges

1. DROP Function Syntax

DROP(array, rows, [columns])

2. DROP Function Arguments

array	Required. The source cell range or array.
rows	Required. The number of contiguous rows to remove, a negative number removes contiguous rows from the end.
[columns]	Optional. The number of contiguous columns to remove, a negative number removes contiguous rows from the end.

3. DROP Function example

DROP function example1

The picture above shows how the DROP function removes the two first rows from cell range B2:D5.

Dynamic array formula in cell B9:

=DROP(B2:D5, 2)

Note! If any of the values in the source range change, the values in the DROP function output change as well.

3.1 Explaining formula

Step 1 - DROP function

DROP(array, rows, [columns])

Step 2 - Populate arguments

array - B2:D5
rows - 2
[columns] -

Step 3 - Evaluate function

DROP(B2:D5, 2)

becomes

DROP({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2)

and returns

{19, 92, 62;
37, 63, 45}

4. DROP Function example - remove columns

DROP function remove columns

The image above demonstrates how to remove columns using the DROP function.

Formula in cell B6:

=DROP(B2:D5, , 2)

The DROP function removes the two first columns from cell range B2:D5.

5. DROP Function example - return rows and columns

DROP function remove columns and rows

The image above demonstrates how to remove both rows and columns using the DROP function.

Formula in cell B9:

=DROP(B2:D5, 2, 2)

DROP(array, rows, [columns])

6. DROP Function example - negative values

DROP function negative numbers

The DROP function lets you use negative arguments, this means that the function removes rows/columns from the end.

Formula in cell B9:

=DROP(B2:D5, -2, -2)

DROP(array, rows, [columns])

7. DROP function error

DROP function errors

The DROP function returns a #VALUE! error when no values are displayed.

8. DROP function - values in random order

DROP function random order 1

The image above shows a formula that returns values in random order from the two first rows in a 2D cell range.

Dynamic array formula in cell B8:

=LET(z, DROP(B2:D5, 2), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

8.1 Explaining formula in cell B8

Step 1 - Remove two first rows

DROP(B2:D5, 2)

becomes

DROP({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2)

and returns

{19, 92, 62;
37, 63, 45}.

Step 2 - Rearrange values to a single row

The TOROW function rearranges values from a 2D cell range or array to a single row.

TOROW(array, [ignore], [scan_by_col])

TOROW(DROP(B2:D5,2))

becomes

TOROW({19, 92, 62;37, 63, 45})

and returns

{19, 92, 62, 37, 63, 45}.

Step 3 - Count cells

The COLUMNS function returns the number of columns in a given cell range or array.

COLUMNS(array)

COLUMNS(TOROW(DROP(B2:D5,2)))

becomes

COLUMNS({19, 92, 62, 37, 63, 45})

and returns 6.

Step 4 - Count rows

The ROWS function returns the number of rows in a given cell range or array.

ROWS (array)

ROWS(DROP(B2:D5,2))

becomes

ROWS({19, 92, 62;37, 63, 45})

and returns 2.

Step 5 - Create random decimal numbers

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(,COLUMNS(TOROW(DROP(B2:D5,2))))

becomes

RANDARRAY(,6)

and returns

{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.

Step 6 - Rearrange values in random order

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(TOROW(DROP(B2:D5,2)),RANDARRAY(,COLUMNS(DROP(B2:D5,2))))

becomes

SORTBY({19, 92, 62, 37, 63, 45}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{19, 92, 62, 37, 63, 45}.

Step 7 - Rearrange values to the original array size

The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPCOLS(vector, wrap_count, [pad_with])

WRAPCOLS(SORTBY(TOROW(DROP(B2:D5,2)),RANDARRAY(,COLUMNS(TOROW(DROP(B2:D5,2))))),ROWS(DROP(B2:D5,2)))

becomes

WRAPCOLS({19, 92, 62, 37, 63, 45}, ROWS(DROP(B2:D5,2)))

becomes

WRAPCOLS({19, 92, 62, 37, 63, 45}, 2)

and returns

{19, 92, 62; 37, 63, 45}.

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

WRAPCOLS(SORTBY(TOROW(DROP(B2:D5, 2)), RANDARRAY(, COLUMNS(TOROW(DROP(B2:D5, 2))))), TOROW(DROP(B2:D5, 2)))

becomes

LET(z, DROP(B2:D5, 2), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

9. DROP Function - multiple source ranges

DROP function multiple source ranges

The picture above shows a formula that merges three non-contiguous cell ranges and removes the last column.

Dynamic array formula in cell B9:

=DROP(VSTACK(B3:D6, F3:H6, J3:L6), , -)

9.1 Explaining formula

Step 1 - Stack values horizontally

The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

VSTACK(array1, [array2],...)

VSTACK(B3:D6, F3:H6, J3:L6)

becomes

VSTACK({"Peach", 43, 1.03;"Blueberry", 39, 1.48;"Apple", 46, 1.1;"Grapefruit", 14, 0.72}, {"Mandarin", 29, 0.78;"Raspberry", 33, 1.07;"Plum", 25, 0.9;"Mango", 37, 1.13}, {"Pear", 17, 0.63;"Orange", 31, 1.06;"Lime", 17, 1.27;"Kiwi", 45, 0.58})

and returns

{"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}

Step 2 - Remove last column

DROP(VSTACK(B3:D6, F3:H6, J3:L6), , 2)

becomes

DROP({"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}, , 2)

and returns

{"Peach", 43;
"Blueberry", 39;
"Apple", 46;
"Grapefruit", 14;
"Mandarin", 29;
"Raspberry", 33;
"Plum", 25;
"Mango", 37;
"Pear", 17;
"Orange", 31;
"Lime", 17;
"Kiwi", 45}

Functions in this article

DROP LET TOROW WRAPCOLS SORTBY RANDARRAY COLUMNS ROWS VSTACK

Functions in 'Array manipulation' category

The DROP function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the DROP function

Table of Contents

1. DROP Function Syntax

2. DROP Function Arguments

3. DROP Function example

3.1 Explaining formula

Step 1 - DROP function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. DROP Function example - remove columns

5. DROP Function example - return rows and columns

6. DROP Function example - negative values

7. DROP function error

8. DROP function - values in random order

8.1 Explaining formula in cell B8

Step 1 - Remove two first rows

Step 2 - Rearrange values to a single row

Step 3 - Count cells

Step 4 - Count rows

Step 5 - Create random decimal numbers

Step 6 - Rearrange values in random order

Step 7 - Rearrange values to the original array size

Step 8 - Shorten formula

9. DROP Function - multiple source ranges

9.1 Explaining formula

Step 1 - Stack values horizontally

Step 2 - Remove last column

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment