TAKE DROP

Sometimes we want to grab just the latest few rows of a report, remove some headers, or extract a specific section of data—like Q2 sales—from a larger table. Excel’s TAKE and DROP functions are perfect for these tasks. In this post, we’ll walk through real examples (based on our sample file) to see how these functions can help us dynamically manage our data without hardcoding row or column references.

Video

---

Understanding the TAKE Function

TAKE is used to return a specific number of rows or columns from the start or end of a range.

TAKE Function Returns: A subset of an array based on a specified number of rows and/or columns.

Syntax:

TAKE(array, [rows], [columns])

• array – The source range or array
• rows – Number of rows to return (positive = from top, negative = from bottom)
• columns – (Optional) Number of columns to return (positive = from left, negative = from right)

---

Exercise 1: Using TAKE to Get the First or Last Rows

In the worksheet named Exercise 1, we have a simple monthly sales table with two columns: Month and Sales.

Let’s say we want to return just the first 3 months from this list.

Formula in cell E8:

=TAKE(B7:C19, 3)

How about the last 3 months?

Use a negative value:

=TAKE(B7:C18, -3)

This gives us October, November, and December:

---

Taking Specific Columns

TAKE can also isolate columns, either from the left or right.

First column only (i.e., months):

=TAKE(B7:C18, , 1)

Last column only (i.e., sales):

=TAKE(B7:C18, , -1)

Pro tip: If you omit the rows argument, TAKE will include all rows. You can just use the columns argument to isolate a column.

---

Understanding the DROP Function

While TAKE retrieves rows or columns, DROP removes them from the beginning or end of a range.

DROP Function Returns: A range with a specified number of rows and/or columns removed from the beginning or end.

Syntax:

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

• array – The source range or array
• rows – Number of rows to exclude (positive = from top, negative = from bottom)
• columns – (Optional) Number of columns to exclude (positive = from left, negative = from right)

---

Exercise 2: Using DROP to Remove Headers or Quarters

Let’s say our original data includes a header row, and we want to remove it.

Formula in cell F7:

=DROP(B6:C18, 1)

This skips the header and gives us just the data.

Want to skip the first quarter (3 months)?

=DROP(B7:C18, 3)

This leaves behind January through March, and returns April through December:

Remove the last 3 months?

=DROP(B7:C18, -3)

We’re left with January through September.

---

Dropping Specific Columns

Want to exclude the Month column and return just the Sales?

=DROP(B7:C18, 0, 1)

Want to exclude the Sales column instead?

=DROP(B7:C18, 0, -1)

As you can see, just like TAKE, DROP works with both rows and columns—depending on whether you use positive or negative values.

---

Combining TAKE and DROP to Extract Middle Sections

Now, what if we want a specific middle chunk of the table—like Q2 (April, May, June)? This is where combining the two functions shines.

We can first use TAKE to get the first 6 months, and then use DROP to remove the first 3:

Formula in cell F7 (Exercise 3):

=DROP(TAKE(B7:C18, 6), 3)

Alternatively, we could reverse it—first DROP the first 3 months, then TAKE the next 3:

=TAKE(DROP(B7:C18, 3), 3)

Either way, we’re isolating Q2 dynamically.

---

Why Use TAKE and DROP?

You might be wondering: why not just select the rows manually?

Let’s look at a few benefits of using these dynamic functions:

• Dynamic Updates: If your source data is in a table that grows over time, formulas using TAKE or DROP automatically adjust.
• Less Maintenance: No need to update row references when new data is added.
• Clarity in Logic: Your formulas clearly describe the intention (e.g., “get last 3 rows” instead of a fixed A15:A17 reference).

For example, if new months are added to your sales table, =TAKE(SalesTable, -3) will always return the latest 3 rows—no extra work needed.

---

Conclusion

The TAKE and DROP functions are simple but powerful tools for working with dynamic ranges in Excel. Whether you’re trimming headers, pulling recent transactions, or isolating quarterly data, these functions offer a clean and flexible approach—especially when combined.

Have questions or a creative way you’ve used these functions? Let us know in the comments—we’d love to hear from you!

Sample File

Tip: Make sure you’re using Excel 365 or Excel 2021+ to access TAKE and DROP—these are part of the newer dynamic array functions.

---

FAQ

1. What version of Excel do I need to use TAKE and DROP?
These functions are available in Excel 365 and Excel 2021+ as part of the dynamic array update.

2. Can I use TAKE and DROP with Excel Tables?
Yes, and it’s highly recommended! When your source is a table, these functions automatically adapt to growing or shrinking data.

3. What happens if I ask TAKE to return more rows than exist?
It will just return all the rows—it won’t throw an error.

4. Can I combine TAKE and DROP with other functions?
Absolutely. They work well with SORT, FILTER, INDEX, and others to build more powerful dynamic reports.

5. Can I use TAKE or DROP on columns only?
Yes. You can omit the rows argument and just use the columns argument. For example: =TAKE(range,,1) returns the first column.

6. What’s the difference between using TAKE vs just selecting a range?
TAKE is dynamic. A selected range is static. If your data grows, a static range won’t update—TAKE will.

7. Can I use TAKE/DROP on non-contiguous ranges?
No, these functions only work with continuous arrays or ranges.

8. Can these functions help with dashboards?
Definitely. You can show the latest transactions, top sales, or recent entries dynamically using TAKE and DROP.