Why this matters
Data Analysts spend a lot of time cleaning and exploring datasets. Sorting and filtering help you find the right records fast, spot outliers, prepare data for charts, and create focused views for stakeholders.
- Prioritize support tickets by severity and date
- List top customers by revenue in each region
- Check recent orders over a threshold before reporting KPIs
Concept explained simply
Sorting changes the order of rows based on column values. Filtering hides rows that do not match criteria. Nothing is destroyed; it’s just rearrangement (sort) and selective visibility (filter).
Mental model
Imagine a stack of index cards (rows). Sorting is reordering the whole stack by a label (column). Filtering is keeping only the cards that match a rule and setting the rest aside. When done, you can bring all cards back.
Key tools you will use
- Multi-level sort: Sort by Region A–Z, then by Revenue Z–A
- Custom sort order: e.g., High, Medium, Low
- Filter by text/number/date: Equals, Contains, Between, Before/After
- Filter by color or icon: Keep highlighted rows only
- Formulas for dynamic views: SORT, FILTER, SORTBY (where supported)
Formula patterns you can reuse
- Dynamic sort (Google Sheets and modern Excel):
=SORT(A2:E100, 2, TRUE, 5, FALSE) // sort by column 2 asc, then column 5 desc
- Dynamic filter:
=FILTER(A2:E100, E2:E100="Shipped", C2:C100>=100)
- Sort-by another range (Excel SORTBY):
=SORTBY(A2:E100, E2:E100, -1, B2:B100, 1)
Worked examples
Example 1: Multi-level sort
Data:
Name | Team | Score Aki | Marketing | 87 Bea | Sales | 82 Cam | Marketing | 92 Dev | Sales | 76 Emi | HR | 90
Task: Sort by Team A–Z, then Score Z–A.
Result:
Emi | HR | 90 Aki | Marketing | 87 Cam | Marketing | 92 ← since second key is Score Z–A, Cam should appear before Aki Cam | Marketing | 92 Aki | Marketing | 87 Bea | Sales | 82 Dev | Sales | 76
Note: Ensure the second key is applied properly so Cam (92) appears before Aki (87) within Marketing.
Example 2: Filter by multiple conditions
Transactions:
Txn | Date | Amount | Status T01 | 2025-01-03 | 360 | Shipped T02 | 2025-01-17 | 480 | Pending T03 | 2024-12-27 | 320 | Shipped T04 | 2025-01-09 | 300 | Shipped T05 | 2024-12-15 | 240 | Canceled
Show only Shipped AND Date between 2025-01-01 and 2025-01-31 AND Amount ≥ 300.
Keep: T01, T04.
Example 3: Keep latest record per customer (no formulas)
- Sort by Customer A–Z (primary)
- Then sort by Date Z–A (secondary)
- Use Remove Duplicates on Customer, keeping the first row per customer
Result: one most recent row per customer. Do not rely on single-column sorts; always multi-level for correctness.
Step-by-step: How to sort and filter
Sort rows (universal steps)
- Select any cell in the table
- Open Sort dialog (Data → Sort range/table)
- Add primary key (e.g., Region A–Z)
- Add secondary key (e.g., Revenue Z–A)
- Confirm that “My data has headers” is checked if applicable
- Apply
Filter rows (universal steps)
- Enable Filter (Data → Create a filter/Filter)
- Open the filter dropdown on the target column
- Choose Text/Number/Date filters (e.g., Between, Equals, Contains)
- Combine conditions across columns (they AND together)
- Clear filter to show all rows again
Formula-driven views (dynamic)
- Place formula output on a new sheet or to the right of your data
- Use SORT/FILTER to create always-updated lists for dashboards
- Keep source data unchanged for auditability
Data hygiene that makes sorting/filtering reliable
- Consistent types: Convert numbers stored as text; format dates properly
- Trim spaces: Remove leading/trailing spaces before text sorts
- One header row: Avoid merged cells in headers
- Whole-row operations: Sort entire table, not a single column
- Avoid blank rows inside the table
Exercises
These mirror the interactive tasks below. You can do them in any spreadsheet app. Tip: Copy tables exactly as shown.
Exercise 1 — Multi-level sort (Region then Revenue)
Orders:
OrderID | Region | Rep | Item | Units | UnitPrice | Revenue | OrderDate | Status 1001 | North | Ava | Desk | 3 | 120 | 360 | 2025-01-03 | Shipped 1002 | West | Ben | Chair | 12 | 40 | 480 | 2025-01-17 | Pending 1003 | North | Ava | Chair | 8 | 40 | 320 | 2024-12-27 | Shipped 1004 | East | Diya | Lamp | 20 | 15 | 300 | 2025-01-09 | Shipped 1005 | West | Chen | Desk | 2 | 120 | 240 | 2024-12-15 | Canceled 1006 | South | Luis | Chair | 30 | 40 | 1200 | 2025-01-12 | Shipped 1007 | East | Diya | Desk | 5 | 120 | 600 | 2025-01-18 | Pending 1008 | North | Maya | Lamp | 25 | 15 | 375 | 2024-12-30 | Shipped
Task: Sort by Region A–Z, then Revenue Z–A. What is the OrderID order after sorting?
Exercise 2 — Filter by three conditions
Using the same Orders table:
- Status = "Shipped"
- OrderDate between 2024-12-28 and 2025-01-18 (inclusive)
- Revenue ≥ 300
Task: Which OrderIDs remain visible?
Self-checklist before you move on
- I sorted the entire table, not just a column
- I added multiple sort keys correctly (primary, secondary)
- I can apply AND filters across columns
- I can clear filters to return all rows
- I can reproduce the expected OrderID lists
Common mistakes and how to self-check
- Sorting one column only: Always include the whole table to keep rows intact
- Forgetting secondary sort: Add a second key to control ties
- Mixed data types: If 100 and "100" are mixed, sorts and filters behave oddly; fix types
- Hidden leading spaces: Use TRIM or clean the data to avoid incorrect alphabetic order
- Relying on prior order after a new sort: Use multi-level sort; do not assume sort stability
Quick self-audit
- Pick two rows that were adjacent before sorting—are they still adjacent for a valid reason?
- Count visible rows after filtering—does the count match your criteria math?
- Spot-check boundary dates/amounts to ensure inclusivity/exclusivity is correct
Practical projects
- Weekly KPI sheet: Filter current week’s data and sort by impact to produce a short status list
- Customer leaderboard: Multi-level sort by Region then Revenue; produce top 10 per region
- Quality review queue: Filter records with missing fields and sort by oldest date first
Who this is for
- Aspiring and junior Data Analysts
- Anyone preparing datasets for pivot tables, charts, or dashboards
Prerequisites
- Basic spreadsheet navigation (rows, columns, headers)
- Comfort with copy/paste and basic formatting
Learning path
- Sorting basics → multi-level sorts → custom orders
- Filtering basics → AND/OR combinations → by color/date/number
- Dynamic formulas: SORT, FILTER, SORTBY for live views
- Combine with pivot tables and charts
Next steps
- Practice with larger datasets (hundreds to thousands of rows)
- Learn to record repeatable steps (document your criteria)
- Move on to Pivot Tables and basic formulas (SUMIF, COUNTIF)
Mini challenge
Create a dynamic "Shipped this month" view using FILTER, then SORT it by Revenue Z–A. Add a second FILTER for a specific Region. Screenshot your top 5 rows.
Tip: Save your progress
Your Quick Test is available to everyone. Log in to have your progress saved and tracked.