6. Tables, Sorting and Filtering
6.5 AutoFilter
Steps in Excel
- Click in the data › Data › Sort & Filter › Filter (Ctrl + Shift + L). Tables have filter buttons already.
- Click the ▾ on City › untick (Select All) › tick Pune and Nashik › OK.
- Text Filters › Contains…
Road(College Road, Gangapur Road, Hotgi Road). - Number Filters › Top 10… (e.g. Top 5 Items by Amount), Above Average, Between.
- Date Filters › This Month, Last Week, Between, or group by year/month in the list.
- Filter by selected cell's value: right-click a cell › Filter › Filter by Selected Cell's Value.
- Clear: Data › Sort & Filter › Clear; re-apply after data changes with Reapply.
Worked example. Zoya filters: Platform = FreshGo, City = Nagpur, Delivery Mins > 15 → she copies the visible rows (Home › Find & Select › Go To Special › Visible cells only, or Alt + ;) and pastes them into an e-mail to the Sitabuldi store team.
Ravindra Bagale's Tip
When you copy-paste filtered data, hidden rows sometimes come along too (especially grouped/hidden rows) – many students don't realise this. Before copying, press Alt + ; (visible cells only). And don't delete a column while a filter is on – the hidden rows are affected too.
Ravindra Bagale's Tip – मराठी
Filter केलेल्या data वर copy-paste केल्यावर कधी कधी लपलेल्या rows पण जातात (विशेषतः grouped/hidden rows) – बऱ्याच students ना हे कळत नाही. Copy करण्याआधी Alt + ; (visible cells only) दाबा. आणि filter लावून एखादा column delete करू नका – लपलेल्या rows पण affect होतात.
Ravindra Bagale's Tip – हिंदी
Filter किए data को copy-paste करने पर कभी-कभी छुपी rows भी साथ चली जाती हैं (ख़ासकर grouped/hidden rows) – बहुत से students को यह समझ नहीं आता. Copy करने से पहले Alt + ; (visible cells only) दबाओ. और filter लगाकर कोई column delete मत करो – छुपी rows पर भी असर होता है.
Practice task
Show only MarketRush orders above ₹200 from last week. Then show orders whose Area contains "Road". Copy only visible cells to a new sheet.
Loading practice tasks...
- Distinct groups (Easy): How many different values in the "Room type" column?
- List groups (Easy): Unique list of the "Room type" column.
- Filter names (Easy): Names in the "Name" column where "Room type" is "Standard".
- Sum of filtered (Medium): Sum of the "Nights" column where "Room type" is "Deluxe", using FILTER.
- Count filtered (Medium): Rows where "Nights" is above the average of the "Nights" column.
- Sorted values (Medium): the "Nights" column sorted largest first.
- Top value via SORT (Medium): The largest the "Rate per night (£)" column value using SORT and INDEX.
- Second largest (Hard): Second largest the "Nights" column using SORT and INDEX.
- Numbers 1 to 5 (Hard): A spill of numbers 1 to 5.
- Two-condition filter (Hard): Sum of the "Nights" column where "Room type" is "Standard" or "Deluxe".
- Names joined (Hard): Names where "Room type" is "Standard", joined with ", ".
Worked example — autofilter
Why
Sana Sheikh in Pune and Anna Kowalski abroad both need a clear, repeatable way to use autofilter on a Skyline Clinics sheet without breaking totals.
What
Autofilter 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 autofilter.
- Enter a small made-up value for Sana Sheikh using € where money appears.
- Add a second value for Anna Kowalski 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 autofilter is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
