16. Practice Exercises with Answer Hints
16.5 Module 6: Tables, Sorting and Filtering
- Convert Orders to a Table named
tblOrdersand add a Total Row showing Sum of Amount. Hint: Ctrl + T; Table Design › Table Style Options › Total Row. - Sort by City (A–Z), then Amount (largest first). Hint: Data › Sort & Filter › Sort › Add Level.
- Sort cities in a custom order: Pune, Nagpur, Nashik, Sambhaji Nagar, Kolhapur, Solapur. Hint: Sort › Order › Custom List….
- Show only Nashik orders above ₹200. Hint: AutoFilter City = Nashik, Amount › Number Filters › Greater Than 200.
- Copy all Pune or Nagpur Delivered orders to another sheet with Advanced Filter. Hint: criteria range with two rows (Pune/Delivered, Nagpur/Delivered); Data › Advanced › Copy to another location.
- Sum only the visible rows after filtering.
Hint:
=SUBTOTAL(109,tblOrders[Amount])or=AGGREGATE(9,5,range).
Ravindra Bagale's Tip
Many students use SUM on filtered data, and the hidden rows get added too. For filtered data, use SUBTOTAL(109,…) or the Table's Total Row. And before sorting, make sure all the data is selected – otherwise the columns fall out of alignment.
Ravindra Bagale's Tip – मराठी
बरेच students filter लावलेल्या data वर SUM वापरतात आणि hidden rows पण बेरीज होतात. Filter केलेल्या data साठी SUBTOTAL(109,…) किंवा Table चा Total Row वापरा. आणि sort करण्यापूर्वी सगळा data select आहे का ते बघा – नाहीतर columns ची जुळवणी (alignment) तुटते.
Ravindra Bagale's Tip – हिंदी
बहुत से students filter लगे data पर SUM इस्तेमाल करते हैं और hidden rows भी जुड़ जाती हैं. Filter किए data के लिए SUBTOTAL(109,…) या Table की Total Row इस्तेमाल करो. और sort करने से पहले देखो कि सारा data select है – वरना columns का मेल (alignment) टूट जाता है.