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 |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Rohan Patil) are made up for this lesson on advanced filter.
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 के साथ) नहीं चलेगा.
Worked example — advanced filter
Why
Aarti Kulkarni in Pune and Sophie Martin abroad both need a clear, repeatable way to use advanced filter on a NovaCraft Tools sheet without breaking totals.
What
Advanced Filter is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for advanced filter.
- Enter a small made-up value for Aarti Kulkarni using $ where money appears.
- Add a second value for Sophie Martin using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once advanced filter is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of advanced filter on a blank sheet for Skyline Clinics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Priya Deshmukh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Anna Kowalski.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply advanced filter to a 5-row made-up Skyline Clinics table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with advanced filter.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as SkylineClin_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
