TAKE vs FILTER Function in Excel Explained

Hello everyone! Today I compare TAKE vs FILTER function in Excel with simple example, so you can choose right one for your work.

Many people confuse between TAKE and FILTER because both can pull out data from your list. But they work very different! No worry, I explain step by step, very easy. Video below is in Khmer so you can follow along easy.

1. TAKE Formula in Excel

TAKE function is use when you want grab first few row or last few row from your data. You no need to know any condition, you just say "give me top 5 row" or "give me bottom 3 row" — TAKE will do it for you.

Syntax:

=TAKE(array, rows, [columns])

• array — your data range, example A2:D100
• rows — how many row you want take. Put positive number to take from top, put negative number to take from bottom
• columns — optional. How many column you want take

Example:

If you have sales data in A2:D20 and you want see only top 5 row, you type:

=TAKE(A2:D20, 5)

If you want see last 3 row, you type:

=TAKE(A2:D20, -3)

Very simple right? TAKE just grab row by position, no condition need.

💡 Tip: TAKE function only available in Excel 365 and Excel 2021 or newer. If you use old Excel, this formula not work. You need upgrade or use other method.

2. FILTER Formula in Excel

FILTER function is use when you want pull out data that match your condition. For example, you want see only sale from "Phnom Penh" or only product with price more than $50 — FILTER is perfect for this.

Syntax:

=FILTER(array, include, [if_empty])

• array — your data range
• include — your condition, example B2:B100="Phnom Penh"
• if_empty — optional. What to show if no result found, you can put "No data"

Example:

If you have data in A2:D100 and column B have city name, and you want see only row where city is "Phnom Penh":

=FILTER(A2:D100, B2:B100="Phnom Penh", "No data")

Boom! Only Phnom Penh row show up. All other row hide automatic.

💡 Tip: You can combine two condition with * (mean AND) or + (mean OR). Example: =FILTER(A2:D100, (B2:B100="Phnom Penh")*(C2:C100>50)) — this give you only Phnom Penh sale AND price more than 50.

3. TAKE vs FILTER — What is Difference?

Simple rule to remember:

• Use TAKE when you want "first N" or "last N" row → no condition need
• Use FILTER when you want row that match something → need condition

💡 Tip: You can combine TAKE and FILTER together! For example: =TAKE(FILTER(A2:D100, B2:B100="Phnom Penh"), 5) — this filter Phnom Penh first, then take only top 5 result. Very powerful!

📚 Useful Resources

• How to Count and Find Unique Value in Excel
• How to Calculate Number of Students (Male/Female/Pass/Fail) in Excel
• Top Microsoft Excel Tips and Tricks Every Spreadsheet User Should Know
• Free Download PDF - 50 Popular Excel Formulas Cheatsheet (Khmer Language)
• Essential Excel Keyboard Shortcuts Every User Should Know