Excel का Filter Function नहीं सीखा तो सब बेकार..

Overview Syntax Try it live Video Examples Things to know

Dynamic Array Function

The FILTER function,
finally making sense.

One formula, zero helper columns. FILTER pulls out exactly the rows you care about from any range or table — and keeps pulling, automatically, as your data changes.

SalesLog.xlsx

H4 fx =FILTER(A5:D20, C5:C20=H2, "")

	Region	Rep	Product	Units

Overview

What FILTER actually does

Think of FILTER as a bouncer for your data. You hand it a range, tell it the condition a row has to meet to get in, and it returns only the rows that pass — as a single spilled array, right on the sheet. No pivot table, no AutoFilter dropdown, no copy-pasting.

Because the result is a dynamic array, Excel automatically sizes the output range the moment you hit Enter. Add new rows to the source (especially if it lives inside a proper Excel Table), and the filtered result grows or shrinks right along with it — no re-selecting ranges, ever.

---

Syntax

How the formula is built

=FILTER(array, include, [if_empty])

Argument	What it's for
arrayRequired	The range you want to filter — this can be a plain range like A5:D20 or a structured Table reference.
includeRequired	A TRUE/FALSE array the same height (or width) as array. Any row lined up with TRUE makes it into the result; FALSE gets dropped.
if_emptyOptional	What to show if nothing matches. Skip this and a no-match scenario throws a #CALC! error, since Excel can't return a truly empty array.

---

Hands-on

Try it yourself — no Excel required

This is a live simulation of the same 16-row sales log from the example above. Change the dropdowns and watch the formula bar — and the spilled result — update in real time.

Product Any Apples Oranges Bananas Pears

Region Any East West North South

Match logic

Sort by Units

H4 =FILTER(A5:D20,,"")

---

Watch it in action

Video walkthrough

A short screen recording walking through everything above inside a real workbook. Drop your video file in and this spot lights right up.

Your walkthrough video goes here — swap the placeholder
for an <iframe> or <video> tag once it's uploaded.

Recommended: 16:9, under 3 minutes, captions on.

---

Worked examples

Stacking FILTER with real conditions

Match two conditions at once

Multiply two comparisons together and FILTER only keeps rows where both are TRUE — Apples AND East region.

=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")

Filter, then sort the result

Wrap the whole thing in SORT to rank the filtered rows by Units, largest first.

=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

Match either condition

Swap the multiplication for addition and it becomes an OR — Apples OR East region, either one qualifies.

=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)

---

Good to know

A few things that trip people up

• No matches, no if_empty → you'll get a #CALC! error. Excel can't hand back a genuinely empty array, so always set the third argument if there's any chance of a miss.
• The include array must resolve to TRUE/FALSE. If any part of it errors out or can't convert to a Boolean, the whole FILTER result errors too.
• Tables auto-resize the output. Base array on a structured Table reference and the spilled result grows or shrinks with your data automatically.
• Cross-workbook links need both files open. Close the source workbook and a linked FILTER formula returns #REF! the next time it recalculates.
• No absolute references needed. Since the formula lives in one cell and spills outward, there's nothing to lock with $ signs when copying it around.

Built for practice, not production data. =FILTER() works the same way in Excel 365 and Excel for the web.

✍️ Written by Harsh Saxena for Harsh Decode — more Excel guides on the blog.

WhatsApp pe Share karo