How to use the DROP function
The DROP function removes a given number of rows or columns from a 2D cell range or array.
Dynamic array formula in cell B9:
The DROP function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
- 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
- Get Excel *.xlsx file
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
The picture above shows how the DROP function removes the two first rows from cell range B2:D5.
Dynamic array formula in cell B9:
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
The image above demonstrates how to remove columns using the DROP function.
Formula in cell B6:
The DROP function removes the two first columns from cell range B2:D5.
5. DROP Function example - return rows and columns
The image above demonstrates how to remove both rows and columns using the DROP function.
Formula in cell B9:
DROP(array, rows, [columns])
6. DROP Function example - negative values
The DROP function lets you use negative arguments, this means that the function removes rows/columns from the end.
Formula in cell B9:
DROP(array, rows, [columns])
7. DROP function error
The DROP function returns a #VALUE! error when no values are displayed.
8. DROP function - values in random order
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:
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
The picture above shows a formula that merges three non-contiguous cell ranges and removes the last column.
Dynamic array formula in cell B9:
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}
Useful resources
DROP function - Microsoft support
Excel DROP function to remove rows or columns from range or array
'DROP' function examples
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]
Functions in 'Array manipulation' category
The DROP function function is one of many functions in the 'Array manipulation' category.
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