Excel 365 TAKE Function Explained: Extract Top or Bottom Rows Easily

—

by

Himanshu
in

The TAKE function in Excel 365 is one of the powerful Dynamic Array functions introduced to make data extraction easier and cleaner.

🧠 What is the TAKE Function?

The TAKE function allows you to extract a specific number of rows or columns from the beginning or end of a range or array.

It’s extremely useful when you want to:

  • Limit results (like top 5 or last 3 rows)
  • Display recent data
  • Dynamically filter table portions

🔧 Syntax of TAKE

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

📌 Arguments:

ParameterDescription
arrayThe range or array to extract data from
rows(Optional) Number of rows to take. Positive = from top, Negative = from bottom
columns(Optional) Number of columns to take. Positive = from left, Negative = from right

✅ Practical Examples of TAKE in Excel 365

🔸 Example 1: Get the Top 5 Sales Entries

If you have a list of sales data in range A2:B20 (Product, Sales):

=TAKE(A2:B20, 5)

✅ Returns the first 5 rows from the dataset (top 5 sales records).

🔸 Example 2: Get the Last 3 Rows

To extract the last 3 rows from that same range:

=TAKE(A2:B20, -3)

✅ Returns rows 18 to 20 from the array.

🔸 Example 3: Take First 2 Columns Only

If your data range is A1:E10, and you want only the first 2 columns:

=TAKE(A1:E10,,2)

✅ Returns columns A and B with all 10 rows.

🔸 Example 4: Last 5 Rows, Last 2 Columns

For a full dynamic slice (e.g., a summary report):

=TAKE(A2:E100, -5, -2)

✅ Takes the last 5 rows and last 2 columns from your dataset.

🛑 Notes

  • You can omit either the rows or columns argument if not needed.
  • Works only in Excel 365 and Excel for the Web.
  • Output updates dynamically if the source array changes.
  • Compatible with other dynamic functions like SORT, FILTER, UNIQUE.

🧠 Combine with Other Functions

=TAKE(SORT(A2:B100, 2, -1), 3)

✅ Sorts the data in descending order by Sales (column 2), and returns the top 3 results.

📌 Real-Life Use Cases

  • Display Top N performers from a team list
  • Show Latest 5 transactions in a bank log
  • Get first 2 columns from a wide dataset (e.g., IDs & names)
  • Build a summary dashboard showing recent trends

🎓 Want to Master Excel’s Smartest Features?

Learn to use TAKE, DROP, SORT, FILTER, XLOOKUP, LAMBDA, and more with real-life dashboards and projects.

🔗 Mastering MS Excel – A Comprehensive Training Course

✅ What you’ll learn:

  • Excel 365’s latest tools
  • Powerful automation techniques
  • Real-world problem-solving with formulas
  • BONUS: Excel dashboards, VBA, and charts

🎯 Click Here to Enroll Now

On sale products