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) टूट जाता है.
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 — module 6 tables sorting and filtering
Why
Rohan Patil in Pune and James O'Neill abroad both need a clear, repeatable way to use module 6 tables sorting and filtering on a HomeNest Supplies sheet without breaking totals.
What
Module 6 Tables Sorting And Filtering 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 module 6 tables sorting and filtering.
- Enter a small made-up value for Rohan Patil using £ where money appears.
- Add a second value for James O'Neill 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 module 6 tables sorting and filtering 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.
