Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.5 Module 6: Tables, Sorting and Filtering

By Last updated:

  1. Convert Orders to a Table named tblOrders and add a Total Row showing Sum of Amount. Hint: Ctrl + T; Table Design › Table Style Options › Total Row.
  2. Sort by City (A–Z), then Amount (largest first). Hint: Data › Sort & Filter › Sort › Add Level.
  3. Sort cities in a custom order: Pune, Nagpur, Nashik, Sambhaji Nagar, Kolhapur, Solapur. Hint: Sort › Order › Custom List….
  4. Show only Nashik orders above ₹200. Hint: AutoFilter City = Nashik, Amount › Number Filters › Greater Than 200.
  5. 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.
  6. Sum only the visible rows after filtering. Hint: =SUBTOTAL(109,tblOrders[Amount]) or =AGGREGATE(9,5,range).

Ravindra Bagale's Tip

Khup students filter lavlelya data var SUM vapartat aani hidden rows pan berij hotat. Filter kelelya data sathi SUBTOTAL(109,…) kiwa Table cha Total Row vapra. Aani sort karnyapurvi sagla data select aahe ka – nahitar columns chi julavni (alignment) tutte.