9.2 FILTER
Quick answer: the short, self-contained version of this lesson is the guide Excel FILTER Function (Dynamic Array) for Beginners.
FILTER with two conditions with example — in 30 seconds
What: FILTER returns only the rows that pass your test, and the result spills into the cells below.
Why: A live list on a report sheet updates by itself when new data arrives. No more copy, filter, paste.
Tiny example: Pune AND Delivered gives 3 rows: BLK-1001, AMN-1003, BLK-1006. =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))
FILTER with two conditions with example — 30 सेकंदात
काय: FILTER तुमच्या test मध्ये pass होणाऱ्या rows च देतो, आणि result खालच्या cells मध्ये spill होतो.
का: Report sheet वरची live list नवा data आला की आपोआप update होते. Copy, filter, paste बंद.
छोटं उदाहरण: Pune AND Delivered मुळे 3 rows येतात: BLK-1001, AMN-1003, BLK-1006. =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))
FILTER with two conditions with example — 30 सेकंड में
क्या: FILTER सिर्फ़ आपके test में pass होने वाली rows देता है, और result नीचे की cells में spill होता है.
क्यों: Report sheet की live list नया data आने पर अपने आप update होती है. Copy, filter, paste बंद.
छोटा उदाहरण: Pune AND Delivered से 3 rows आती हैं: BLK-1001, AMN-1003, BLK-1006. =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))
First look at the data (before → after):
Expected result — Spilled result (selected columns)
| Order ID | City | Amount | Status |
|---|---|---|---|
| BLK-1001 | Pune | 64 | Delivered |
| AMN-1003 | Pune | 110 | Delivered |
| BLK-1006 | Pune | 150 | Delivered |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Meera Joshi) are made up for this lesson on filter.
Before table and formula as text
Before — tblMini columns used
| Order ID | City | Status |
|---|---|---|
| BLK-1001 | Pune | Delivered |
| BLK-1002 | Nashik | Delivered |
| AMN-1003 | Pune | Delivered |
| BLK-1004 | Nagpur | Cancelled |
| AMN-1005 | Kolhapur | Delivered |
| BLK-1006 | Pune | Delivered |
| BLK-1007 | Solapur | Delivered |
| AMN-1008 | Sambhaji Nagar | Returned |
| BLK-1009 | Pune | Cancelled |
| BLK-1010 | Nashik | Delivered |
About this data: Demo table only — not real customer or company data. Built to practise filter.
Formula / click path
K2: =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))
* = AND · + = OR
Do it step by step:
- Click K2 on an empty area of the sheet.
- Type
=FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered")). - Press Enter. The rows spill down with a blue border.
- Wrap each test in brackets. The * between them means both must be TRUE.
- Add a third argument like
"No rows"to show a message when nothing matches.
स्टेप बाय स्टेप करून बघा:
- Sheet च्या रिकाम्या भागात K2 वर click करा.
=FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))type करा.- Enter दाबा. Rows निळ्या border सह खाली spill होतात.
- प्रत्येक test brackets मध्ये ठेवा. मधला * म्हणजे दोन्ही TRUE हवेत.
- काहीच न जुळल्यास message दाखवायला
"No rows"सारखा तिसरा argument द्या.
स्टेप बाय स्टेप करके देखें:
- Sheet के खाली हिस्से में K2 पर click करें.
=FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered"))type करें.- Enter दबाएँ. Rows नीले border के साथ नीचे spill होती हैं.
- हर test को brackets में रखें. बीच का * यानी दोनों TRUE होने चाहिए.
- कुछ न मिलने पर message दिखाने के लिए
"No rows"जैसा तीसरा argument दें.
=FILTER(array, include, [if_empty])
| Need | Formula |
|---|---|
| All Pune orders | =FILTER(tblMini, tblMini[City]="Pune") |
| Pune AND Delivered | =FILTER(tblMini, (tblMini[City]="Pune")*(tblMini[Status]="Delivered")) |
| Pune OR Nashik | =FILTER(tblMini, (tblMini[City]="Pune")+(tblMini[City]="Nashik")) |
| Orders > value in cell M1 | =FILTER(tblMini, tblMini[Amount]>M1, "No orders") |
| Only some columns | =FILTER(tblMini[[Order ID]:[City]], tblMini[Mins]>15) |
| Text contains "Fruit" | =FILTER(tblMini, ISNUMBER(SEARCH("Fruit", tblMini[Category]))) |
About this data: Made-up rows so you can copy the pattern for filter. Currencies may use ₹, £, € or $.
Worked example. Pune and Delivered returns three rows: BLK-1001 (₹64), AMN-1003 (₹110), BLK-1006 (₹150). If the criteria match nothing and if_empty is missing, FILTER returns #CALC! – always give if_empty.
Ravindra Bagale's Tip
In FILTER, * is for AND and + is for OR – many students use them the wrong way round, or use the AND() function, which doesn't work on arrays. Write it as (cond1)*(cond2), with each condition in brackets. And give if_empty, otherwise an empty result shows #CALC!.
Ravindra Bagale's Tip – मराठी
FILTER मध्ये AND साठी * आणि OR साठी + – हे बरेच students उलटे वापरतात किंवा AND() function वापरतात, जो array वर चालत नाही. (cond1)*(cond2) असंच लिहा, प्रत्येक condition bracket मध्ये. आणि if_empty द्या, नाहीतर रिकाम्या result ला #CALC! दिसतो.
Ravindra Bagale's Tip – हिंदी
FILTER में AND के लिए * और OR के लिए + – बहुत से students इन्हें उल्टा इस्तेमाल करते हैं या AND() function लगा देते हैं, जो array पर नहीं चलता. (cond1)*(cond2) ऐसे ही लिखो, हर condition bracket में. और if_empty दो, वरना खाली result पर #CALC! दिखता है.
Practice task
Build a mini report: a City drop-down in M1 and a FILTER that shows that city's delivered orders with only Order ID, Date and Amount columns; show "No orders" when empty.
Loading practice tasks...
- Pune customers (spill) (Easy): Show customers (B2:B8) where branch is "Pune".
- Approved loan IDs (spill) (Easy): Show A2:A8 where Status is "Approved".
- Big loans (spill) (Easy): Show customers with amount over 1000000.
- Personal loan amounts (spill) (Easy): Show E2:E8 where Type is "Personal".
- Full rows for Goa (spill) (Medium): Show A2:F8 rows where branch is "Goa".
- Approved in Pune (spill) (Medium): Customers in Pune AND Approved.
- Home or Car (spill) (Medium): Customers with Home OR Car loans.
- Total approved (Medium): Total amount of Approved loans.
- Nothing found message (Hard): Show customers from "Delhi", or "None" if none.
- Count pending (Hard): How many loans are Pending?
- Name and amount, Kochi (spill) (Hard): Show customer, branch, type and amount (B2:E8) for Kochi.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
