How to Remove Rows with DROP in Excel

Remove a totals row, strip an ID column and drop an outlier before averaging — without editing the source data.

You will learn to

• Drop the last row of a range
• Drop a leading column
• Drop the top value of a sorted list before averaging

How it works

DROP(array, rows, [columns]) is the opposite of TAKE: it returns everything except the rows or columns you name. =DROP(A2:C11,-1) removes the last row — handy for exported reports that end with a TOTAL line — and =DROP(A2:C10,0,1) removes the first column.

Because DROP works on any array, you can combine it with SORT for quick statistics. =AVERAGE(DROP(SORT(C2:C10,1,-1),1)) sorts the hours from highest to lowest, removes the single highest value and averages the rest, a simple way to stop one unusual shift skewing a result.

=DROP(A2:C11,-1)