A to Z of Excel Functions: The DROP Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the DROP function.

The DROP function

The DROP function excludes a specified number of contiguous rows or columns from either the start or the end of an array.  It has the following syntax:  

The DROP function has the following arguments:

• array: this is required and represents the selected array from which to drop the rows or columns
• rows: this is also required and denotes the number of rows to drop (exclude) from the top.   If this number is negative, the values drop from the bottom of the array
• columns: this is optional and denotes the number of columns to drop (exclude).   If this number is negative, the values drop from the end of the array.

It should be noted that:

• when rows or columns are not provided or missing, all rows and columns are returned
• if the absolute value of rows or columns is greater than the number of rows or columns in the array, then all rows or columns are supposed to be returned, but presently #VALUE! appears to be the favoured treatment
• Excel returns an #CALC! error to indicate an empty array when rows or columns is zero [0]
• Excel returns an #NUM! when array is too large.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.