6. Tables, Sorting and Filtering
6.6 Advanced Filter
Advanced Filter uses a criteria range on the sheet – good for complex AND/OR conditions and for extracting results to another place.
Criteria rules: same row = AND; different rows = OR. Headers must match the data headers exactly.
Criteria range H1:J3:
| City | Amount | Status |
|---|---|---|
| Pune | >200 | Delivered |
| Nashik |
Meaning: (City = Pune AND Amount > 200 AND Status = Delivered) OR (City = Nashik).
Steps in Excel
- Type the criteria headers and conditions above the data or on another area of the sheet.
- Click inside the data › Data › Sort & Filter › Advanced.
- List range: the data (e.g.
$A$1:$I$11); Criteria range:$H$1:$J$3. - Choose Copy to another location › Copy to:
$L$1(you can prepare only the headers you want there). - Tick Unique records only to also remove duplicates in the output › OK.
On the mini dataset of Module 3 the result is: rows with Nashik (BLK-1002, BLK-1010) – no Pune order is both > ₹200 and Delivered (BLK-1009 is ₹220 but Cancelled).
Ravindra Bagale's Tip
In Advanced Filter, many students leave an empty row in the criteria – an empty row means "anything", and all the data comes through! Include only the headers and the condition rows in the criteria range. The header spelling must match the data exactly – "Amount " (with a space) won't work.
Ravindra Bagale's Tip – मराठी
Advanced Filter मध्ये बरेच students criteria मध्ये रिकामी row ठेवतात – रिकामी row म्हणजे "काहीही" आणि सगळा data येतो! Criteria range मध्ये फक्त headers आणि conditions च्या rows घ्या. Headers चं spelling data सारखंच पाहिजे – "Amount " (space सोबत) चालणार नाही.
Ravindra Bagale's Tip – हिंदी
Advanced Filter में बहुत से students criteria में खाली row छोड़ देते हैं – खाली row का मतलब "कुछ भी" और सारा data आ जाता है! Criteria range में सिर्फ़ headers और conditions वाली rows लो. Headers की spelling data जैसी ही होनी चाहिए – "Amount " (space के साथ) नहीं चलेगा.
Practice task
Extract to a new place: (Blinkit AND Fruits) OR (Amazon Now AND Amount ≥ ₹200). Then use Advanced Filter with Unique records only to list distinct City–Area pairs.