How to use the DROP function

What is the DROP function?

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

Use the drop function if know which columns or rows you want to remove, it removes all the rows or columns up to the specified number starting from 1 to n where n is the given number. The DROP function is available to Excel 365 users and is in the "Array manipulation" category.

1. Syntax

DROP(array, rows, [columns])

Back to top

2. 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.

Back to top

3. Example

The picture above shows how the DROP function removes the first two rows from cell range B2:D5. For example, number 2 removes both the first row and the second row from the source data range. The DROP function is really useful in combination with the LAMBDA functions SCAN, MAP, REDUCE, BYROW , and BYCOL functions.

Dynamic array formula in cell B9:

B2:D5 contains 4 rows, the output in cell B9 contains only the last two rows in cell B2:D5. This is because the DROP function removes rows from the top and columns from the left. There is an exception to this demonstrated in section 6 below. Hint! Negative values.

The DROP function returns a dynamic array formula meaning that it returns more than one value if needed, however, entering a dynamic array formula is easy. No difference to entering a regular formula, simply press Enter when you are done.

The DROP function returns a #SPILL! error if a cell is not empty in the destination range, in other words, if a cell below C9 has a value then the formula can't show all the values in the array. This results in a #SPILL! error.

To fix this problem you  need to delete cells containing values so the formula works as intended.

3.1 Explaining formula

Step 1 - DROP function

the DROP function has three arguments, the first and second argument are required and the third argument is optional.

DROP(array, rows, [columns])

Step 2 - Populate arguments

Cell range B2:D5 is used as the array argument, 2 is the rows argument and I leave the columns argument empty.

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

Step 3 - Evaluate function

The following lines show what happens in detail when the DROP function is evaluated in Excel.

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}

Back to top

4. Example - remove columns

The image above demonstrates how to remove columns using the DROP function. The image above shows cell range B2:D5 populated with values. The DROP function removes the two first columns from cell range B2:D5 and returns the third column to cell B6 and cells below as far as needed.

Formula in cell B6:

The DROP function has three arguments, the third one is columns and it is optional. The second argument is rows and is also optional if you specify the third argument. This example demonstrates this, the second argument is not specified so the function removes no rows only columns.

Back to top

5. Example - remove both rows and columns

The image above demonstrates how to remove both rows and columns using the DROP function. Cell range B2:D5 contains values, I have numbered the rows red and they are located to the right of the cell range. The rows argument contains two meaning both the first row and second row is removed from the final array.

The columns are numbered 1 and 2 counting from left to right, they are red. The columns argument contains 2 as well, this means that the first and second column are removed. I have drawn red boxes around the first and second column and the first and second row. The numbers left are in the blue box, they are displayed in cell B9 and cells below.

Formula in cell B9:

DROP(array, rows, [columns])

The DROP function is really useful for removing specific rows and columns in a given array, this was really hard  in previous Excel versions and required lots of functions if even possible.

Back to top

6. Example - negative values

The DROP function lets you use negative arguments, this means that the function removes rows/columns from the end. Cell range B2:D5 contains values, the drop function has three arguments, the second argument is row and is -2 in this example. The third argument is columns and is also -2.

Formula in cell B9:

DROP(array, rows, [columns])

Negative value in the row argument makes the function count from the bottom up, this means that -2 removes the last row and the second last row.

This is also true for the columns argument, -2 removes the last column and the second last column. This leaves us with the first value and the second value, they are in a blue rectangle displayed in the image above. These values are returned from the DROP function in cell B9. This is useful if you need to remove the last column or row or both, no need to count or calculate the number of rows and columns in order to remove the correct ones.

Back to top

7. DROP function error

The DROP function returns a #VALUE! error when no values are displayed. This example, demonstrated in the image above, shows a DROP function using arguments that match or exceed the number of rows and columns in the source data range B2:D5.

Since all values are removed from the source cell range B2:D5 the DROP function returns a #VALUE! error simply because there are no values to display.

Back to top

8. Example - 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. Cell range B2:D5 contains values, the formula removes the two first rows in B2:D5 and returns the remaining values in random order.

The resulting array has the same size as the source cell range excluding the two first rows. The formula changes the order randomly every time you press function key F9.

Dynamic array formula in cell B8:

This formula is useful if you want to randomize the order for a specific number of rows or columns and not the entire source array.

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)))

Back to top

9. Example - multiple source ranges

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

The image above describes three non-contiguous cell ranges in B3:D6, F3:H6, and J3:L6. The formula merges these three cell ranges using the VSTACK function vertically. The DROP function then removes the last column from the merged arrays.

Dynamic array formula in cell B9:

For example, items, quantity and price are the table headers and they are the same for each table range B3:D6, F3:H6, and J3:L6. After mergin the three arrays and removing the last column which is the price the formula returns only the items and quantities to cell B9 and cells below and to the right as far as needed.

This formula is useful if you have data sets located in different worksheets and wants to merge the data and remove specific columns that you don't need.

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}

Back to top

Useful resources

DROP function - Microsoft support
Excel DROP function to remove rows or columns from range or array

'DROP' function examples

Functions in 'Array manipulation' category

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

Excel function categories

Excel categories

Leave a Comment