Part III: Data Management

Sorting and Filtering

🎯 Learning Objectives

• Sort data in ascending (A–Z) and descending (Z–A) order
• Perform multi-level sorting (e.g., by Department then by Salary)
• Sort by cell color, font color, and conditional formatting icons
• Apply AutoFilter to filter data by value, text, number, and date conditions
• Use Advanced Filter for criteria ranges, unique records, and copying results to another location
• Master dynamic array functions: SORT(), SORTBY(), and FILTER()

Sort Basics & Multi-Level Sorting

Simple Sort: A–Z and Z–A

Sorting arranges your data in a specific order. Ascending (A–Z) arranges text alphabetically, numbers from smallest to largest, and dates from earliest to latest. Descending (Z–A) does the reverse. Think of it like organizing your school register — names A to Z make it easy to look up any student.

Step-by-Step: Quick Sort

1. Click any cell in the column you want to sort (e.g., click on cell E2 in the Salary column)
2. Go to Data tab → Sort & Filter group
3. Click Sort A to Z (↑) for ascending or Sort Z to A (↓) for descending
4. Excel automatically detects the data range and sorts the entire table by that column

Example 1: Sort employees by Salary (ascending)

Click any cell in the Salary column → Data → Sort A to Z. Result:

Custom Sort & Multi-Level Sort

What if you want to sort by Department first, and then within each department, sort by Salary from highest to lowest? That's multi-level sorting — exactly how HR managers organize payroll reports at companies like TCS and Infosys.

Step-by-Step: Multi-Level Sort

1. Click any cell in your data range
2. Go to Data tab → click Sort (the full Sort button, not the quick A-Z buttons)
3. In the Sort dialog:
   • Sort by: Department → Order: A to Z
   • Click Add Level
   • Then by: Salary → Order: Largest to Smallest
4. Click OK

Example 2: Multi-level sort — Department (A–Z), then Salary (High to Low)

Custom Sort Order

Sometimes alphabetical order isn't what you want. For example, you might want months to sort as Jan, Feb, Mar... not Apr, Aug, Dec. Or departments in a specific business hierarchy: "Management → IT → Finance → HR → Marketing."

Step-by-Step: Custom Sort List

1. Data → Sort → In the Order dropdown, select Custom List...
2. Type your custom order in the List entries box (one item per line): Management, IT, Finance, HR, Marketing
3. Click Add, then OK

Example 3: Sort by custom department order

Custom order: IT, Finance, HR, Marketing. Result: All IT employees appear first, then Finance, then HR, then Marketing — regardless of alphabetical order.

Sort by Cell Color / Font Color / Icon

If you've used conditional formatting to highlight cells (e.g., red for low performers, green for high performers), you can sort by those colors.

1. Data → Sort → In Sort On, choose Cell Color, Font Color, or Conditional Formatting Icon
2. Select the color/icon you want on top
3. Add more levels for each color

Example 4: Sort by cell color

Suppose cells with Salary > ₹80,000 are highlighted green and Salary < ₹65,000 are red. Sort by Cell Color → Green on Top → Red on Bottom.

Example 5: Real-Life — CBSE Board Results Sorting

A CBSE school coordinator receives mark sheets for 500 students. She needs to sort by: Stream (Science, Commerce, Arts) first, then by Total Marks (highest first) within each stream, then by Name (A–Z) for students with the same marks.

Sort levels: 1) Stream → Custom List (Science, Commerce, Arts) 2) Total Marks → Largest to Smallest 3) Name → A to Z

Example 6: Real-Life — Zomato Restaurant Ratings Sort

A Zomato city manager exports restaurant data for Pune. He needs restaurants sorted by Cuisine (A–Z), then Rating (highest first), then Average Cost for Two (lowest first) to recommend affordable highly-rated options.