6. Tables, Sorting and Filtering
6.4 Sorting: Multi-level and Custom Sort
Steps in Excel
- Quick sort: click a cell in a column › Data › Sort & Filter › A→Z or Z→A (whole Table/range is sorted together).
- Multi-level: Data › Sort & Filter › Sort › Sort by City (A to Z) › Add Level › Then by Amount (Largest to Smallest) › Add Level › Then by Order Date (Oldest to Newest) › OK.
- Custom order: in the Sort dialog, Order › Custom List… › type
Pune, Nashik, Nagpur, Sambhaji Nagar, Kolhapur, Solapur› Add › OK. - Sort by colour or icon: Sort On › Cell Color / Font Color / Conditional Formatting Icon.
- Sort left to right: Sort dialog › Options… › Sort left to right.
- Formula alternative (Microsoft 365 / Excel 2021+):
=SORT()/=SORTBY()– Module 9.
Worked example. Ravina's management report must list cities in business priority order (custom list above), within each city the highest orders first. Two levels – City (custom list), Amount (Largest to Smallest).
Ravindra Bagale's Tip
The most dangerous mistake: selecting only one column and sorting – Excel asks "Expand the selection?" and many students click "Continue with the current selection". Then the amounts end up on one side and the orders on the other – the data is ruined! Always click a single cell and sort, or use a Table.
Ravindra Bagale's Tip – मराठी
सगळ्यात धोकादायक चूक: फक्त एकच column select करून sort करणं – Excel "Expand the selection?" विचारतो आणि बरेच students "Continue with the current selection" दाबतात. मग amount एकीकडे आणि order दुसरीकडे – data खराब! नेहमी एकाच cell वर click करून sort करा किंवा Table वापरा.
Ravindra Bagale's Tip – हिंदी
सबसे ख़तरनाक गलती: सिर्फ़ एक column select करके sort करना – Excel पूछता है "Expand the selection?" और बहुत से students "Continue with the current selection" दबा देते हैं. फिर amount एक तरफ़ और order दूसरी तरफ़ – data ख़राब! हमेशा एक ही cell पर click करके sort करो या Table इस्तेमाल करो.
Practice task
Sort tblOrders by Platform, then City in your custom order, then Delivery Mins (fastest first). Sort the Status column in the custom order Delivered, Returned, Cancelled.
Loading practice tasks...
- Total score (Easy): Add up Score in E2:E11. (sheet "Data")
- Average score (Easy): Average of Score in E2:E11, rounded to 1 decimal. (sheet "Data")
- West tickets (Easy): How many rows have Region "West"? (sheet "Data")
- Max score (Easy): Largest Score in E2:E11. (sheet "Data")
- High priority (Easy): How many rows have Priority "High"? (sheet "Data")
- West score (Medium): Total of Score where Region is "West". (sheet "Data")
- High score sum (Medium): Total of Score where Priority is "High". (sheet "Data")
- West high count (Medium): How many rows have Region "West" and Priority "High"? (sheet "Data")
- Score of T05 (Medium): Score for Ticket "T05". (sheet "Data")
- Unique regions (Hard): How many unique Region values? (sheet "Data")
- Share West score (Hard): West score / all score, 3 decimals. (sheet "Data")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
