How to Use DROP Function in Excel + Tips

Hello everyone! Today i will show you how to use DROP function in Microsoft Excel step by step.

1. What is DROP Function in Excel?

The DROP function is a dynamic array function in Excel 365 and Excel 2024. It allows you to remove a specific number of rows and/or columns from the beginning (top) or end (bottom) of a range, returning the remaining data.

Think of DROP as the opposite of the TAKE function:

• TAKE → keeps the first or last N rows.
• DROP → removes the first or last N rows and returns everything else.

For example, you can use DROP to skip header rows, ignore the first few rows of old data, or remove trailing summary rows from a table.

📚 Related Tutorial: Want to extract only the first or last N rows instead? Check out How to Use TAKE Function in Excel!

2. 🛠️ Formula Syntax & Parameters

=DROP(array, [rows], [columns])

• array: Required. The range or array of cells to extract from (e.g., A1:C10).
• [rows]: Optional. The number of rows to remove:
  • Positive number (e.g., 1): Removes rows from the top (first rows).
  • Negative number (e.g., -1): Removes rows from the bottom (last rows).
• [columns]: Optional. The number of columns to remove:
  • Positive number (e.g., 1): Removes columns from the left.
  • Negative number (e.g., -1): Removes columns from the right.

3. 📋 Practical Examples

Example 1: Skip the Header Row When Passing Data to Another Formula

Suppose range A1:C6 contains a product table including a header row on row 1:

=DROP(A1:C6, 1)

👉 Result: Returns only rows 2 to 6 — removes Row 1 (header row) and returns all data rows!

Example 2: Remove the Last Row (Summary/Total Row)

Suppose your table has a Grand Total row at the bottom (Row 7) that you want to exclude from calculations:

=DROP(A1:C7, , -1)

👉 Result: Removes the last row (bottom) of the table and returns all other rows. The first empty , means "don't remove any rows from the top".

Example 3: Remove First Row and First Column (Skip Numbering)

If your table has a row number column (Column A) and a header row (Row 1) you want to skip both:

=DROP(A1:C6, 1, 1)

👉 Result: Returns data starting from Column B, Row 2 — skipping both the header and the numbering column!

Example 4: Combine DROP with SORT for Dynamic Ranking

Remove the header row first, then sort the remaining data by Sales (descending):

=SORT(DROP(A1:C6, 1), 3, -1)

👉 Result: Returns all product data rows sorted from highest to lowest Sales, without the header row!

💡 Quick Tips for Using DROP Function

1. Skip Header Rows Cleanly: Use =DROP(A1:C100, 1) to pass a range into another formula (like SORT or FILTER) without including the header row.
2. Use Negative Numbers to Remove Trailing Rows: =DROP(A1:A20, , -3) removes the last 3 rows — useful for cutting off Grand Total or summary rows at the bottom.
3. Combine with TAKE to Slice the Middle: First DROP rows from the top, then wrap with TAKE to extract a middle section: =TAKE(DROP(A1:C20, 3), 5) skips the first 3 rows and takes the next 5.
4. Excel Version Requirement: DROP requires Microsoft 365, Excel 2024, or Excel for the Web.

📚 Related Resources & Tutorials

• How to Use TAKE Function in Excel
• How to Use FILTER Function in Excel
• How to Use SORT Function in Excel
• How to Use UNIQUE Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗