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
Advanced Filter madhe khup students criteria madhe rikami row thevtat – rikami row mhanje "kahihi" aani sagla data yeto! Criteria range madhe fakt headers aani conditions chya rows ghya. Headers che spelling data sarkhech pahije – "Amount " (space sobat) chalnar nahi.
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.