Can DROP remove both rows and columns at the same time?

Yes. Provide both arguments: =DROP(A1:E5, 2, 1) removes the first 2 rows and the first 1 column in a single operation. The rows argument is always required; columns is optional and defaults to 0.

What happens if I try to drop more rows than the array contains?

Excel returns a #NUM! error because DROP cannot process dimensions that exceed your array size. If your data size varies, calculate the row count with COUNTA and adjust your DROP call conditionally using IF.

How do I drop the last row or the last column instead of the first?

Use a negative number: =DROP(A1:E5, -1) drops the last row, and =DROP(A1:E5, 0, -1) drops the last column. Negative indices count backward from the end of the array.

How is DROP different from TAKE?

DROP excludes rows or columns (everything except what you specify), while TAKE includes only what you specify. On a 5-row array, =DROP(A1:E5, 1) and =TAKE(A1:E5, 4) return the same result: 4 data rows without the header. Use DROP when you know what to remove, and TAKE when you know what to keep.

DROP function

DROP removes specified rows or columns from an array, returning the rest when you need to exclude headers, skip entries, or remove unwanted fields.

=DROP(array, rows, [columns])

Generate a DROP formula

Describe what you need. The generator will reach for DROP where DROP is the right tool, and tell you when it is not.

Arguments

How DROP reads its arguments

Argument	Required	Description
array	Required	The source array or range to modify; can be any rectangular range of cells or values.
rows	Required	Number of rows to exclude (positive removes from top, negative removes from bottom); required and cannot be omitted.
columns	Optional	Number of columns to exclude (positive removes from left, negative removes from right); optional and defaults to 0 if omitted.

Returns

An array with the same structure as the input, minus the specified rows and/or columns.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Remove header row to show just listings

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	$450,000	12
456 Elm Ave	4	3	$625,000	8
789 Pine Rd	2	1	$320,000	24
321 Maple Dr	5	3.5	$875,000	5

=DROP(A1:E5, 1)

Result: 123 Oak St32$450,00012456 Elm Ave43$625,0008789 Pine Rd21$320,00024321 Maple Dr53.5$875,0005

DROP(A1:E5, 1) removes the first row—the header—keeping all four property listings. This is useful when you have column labels you don't need for downstream calculations or when preparing data for a formatted table that doesn't need headers.

2. Remove address column to hide specific property locations

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	$450,000	12
456 Elm Ave	4	3	$625,000	8
789 Pine Rd	2	1	$320,000	24
321 Maple Dr	5	3.5	$875,000	5

=DROP(A1:E5, 0, 1)

Result: BedsBathsList PriceDays on Market32$450,0001243$625,000821$320,0002453.5$875,0005

DROP(A1:E5, 0, 1) keeps all rows but removes the first column (Address) by setting rows to 0 and columns to 1. This is practical when sharing property specifications with a public audience but hiding exact street locations for privacy.

3. Skip header and first listing to paginate through results

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	$450,000	12
456 Elm Ave	4	3	$625,000	8
789 Pine Rd	2	1	$320,000	24
321 Maple Dr	5	3.5	$875,000	5

=DROP(A1:E5, 2)

Result: 456 Elm Ave43$625,0008789 Pine Rd21$320,00024321 Maple Dr53.5$875,0005

DROP(A1:E5, 2) removes the first two rows—the header and the first property—leaving the remaining three listings starting with 456 Elm Ave. Useful for implementing pagination or skipping rows you've already processed.

Common errors

Which DROP error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The rows or columns argument is not a number (for example, a text string, logical value, or error).	Ensure both arguments are numeric. Change =DROP(A1:E5, "one") to =DROP(A1:E5, 1).
#NUM!	The rows or columns value is outside the valid range for your array (for example, dropping 10 rows from a 5-row range).	Adjust the drop count to be less than or equal to your array's dimensions. For a 5-row array, use 1–4 as your rows argument, not 10.
#SPILL!	The resulting array would overflow into cells already occupied by other data, and Excel cannot resolve the conflict automatically.	Move the formula to a location with enough empty space below and to the right, or clear the cells where the result would land.

Tips and when to use something else

Use negative values to drop from the end of the array: =DROP(A1:E5, -1, 0) removes the last row, useful for excluding totals or notes appended at the bottom.
Combine DROP with VSTACK or HSTACK to restructure and merge multiple arrays without manual row or column deletion.
When removing only columns, set rows to 0: =DROP(A1:E5, 0, 2) removes the first two columns while keeping all rows intact.
For conditional filtering, use FILTER instead of DROP—FILTER lets you exclude rows based on criteria (like price > $500k), whereas DROP only removes by position.

Related functions

TAKETAKE extracts a contiguous subset of rows and columns from an array, starting from the beginning or end; use it when you need the first N items or last M items.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.VSTACKVSTACK combines multiple arrays or ranges by stacking them vertically into a single array, placing each input one below the other.HSTACKHSTACK combines multiple arrays horizontally (side by side) into a single rectangular array, expanding shorter arrays with blanks to create a unified output.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

Frequently asked questions

Can DROP remove both rows and columns at the same time?: Yes. Provide both arguments: =DROP(A1:E5, 2, 1) removes the first 2 rows and the first 1 column in a single operation. The rows argument is always required; columns is optional and defaults to 0.
What happens if I try to drop more rows than the array contains?: Excel returns a #NUM! error because DROP cannot process dimensions that exceed your array size. If your data size varies, calculate the row count with COUNTA and adjust your DROP call conditionally using IF.
How do I drop the last row or the last column instead of the first?: Use a negative number: =DROP(A1:E5, -1) drops the last row, and =DROP(A1:E5, 0, -1) drops the last column. Negative indices count backward from the end of the array.
How is DROP different from TAKE?: DROP excludes rows or columns (everything except what you specify), while TAKE includes only what you specify. On a 5-row array, =DROP(A1:E5, 1) and =TAKE(A1:E5, 4) return the same result: 4 data rows without the header. Use DROP when you know what to remove, and TAKE when you know what to keep.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17