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.
Table of Contents
1. Syntax
DROP(array, rows, [columns])
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. |
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}
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.
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.
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.
7. Function error
The DROP function returns a #CALC! 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 B4:D7.
Since all values are removed from the source cell range B2:D5 the DROP function returns a #CALC! error simply because there are no values to display.
7.1 Troubleshooting the error value
When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.
- The first line describes the error if you press with left mouse button on it.
- The second line opens a pane that explains the error in greater detail.
- The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
- This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
- The fifth line lets you edit the formula in the Formula bar.
- The sixth line opens the Excel settings so you can adjust the Error Checking Options.
Here are a few of the most common Excel errors you may encounter.
#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.
#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.
#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.
#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.
#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.
#NAME error - The #NAME error happens if you misspelled a function or a named range.
#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.
#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.
#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.
7.2 The formula returns an unexpected value
To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:
- Select the cell containing the formula you want to examine in detail.
- Go to tab “Formulas” on the ribbon.
- Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control. - Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
- Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
- Press "Close" button to dismiss the dialog box.
There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue.
- Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
- Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
- Press F9: This will calculate and display the result of just that selected portion.
- Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
- Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.
The image above shows cell reference B4:2025-01-13converted to hard-coded value using the F9 key.
Tips!
- View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
- Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
- Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.
Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button
7.3 Other errors
Floating-point arithmetic may give inaccurate results in Excel - Article
Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.
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)))
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}
Useful resources
DROP function - Microsoft support
Excel DROP function to remove rows or columns from range or array
'DROP' function examples
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Functions in 'Array manipulation' category
The DROP function function is one of 11 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