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 = Blinkit, 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 Amazon Now orders above ₹200 from last week. Then show orders whose Area contains "Road". Copy only visible cells to a new sheet.