Concept
1. The golden rule of sorting
Click one cell inside the data (don't select a single column). Excel then selects the whole table and keeps each row together.
If you select just one column and sort, Excel warns "Expand the selection?". Always choose Expand. "Continue with the current selection" sorts only that column and mixes up your data — Rahul's sales end up in Neha's row.
2. Quick sort
Data → A→Z or Z→A sorts by the column of the active cell.
3. Multi-level sort
Data → Sort:
- Sort by Region, A to Z.
- Add Level → then by Sales, Largest to Smallest.
Result: regions together, and within each region the top seller first. Make sure My data has headers is ticked.
You can also sort by Cell Color or Font Color — useful after highlighting duplicates (Lesson 1).
4. Custom sort order
Alphabetical order isn't always right: East, North, South, West or High, Low, Medium look odd.
In the Sort dialog → Order → Custom List… → type your order, one per line:
High
Medium
Low
→ Add → OK. Now sort follows this order. Days of the week and months are already built in.
To reuse a list in every file: File → Options → Advanced → Edit Custom Lists. Bonus: type "High" and drag the fill handle — Excel fills Medium, Low.
5. Filter
Turn on: Ctrl + Shift + L (or Data → Filter). Arrows appear on the headers.
| Filter type | Example |
|---|---|
| Text Filters | Contains "Sharma", Begins with "P10" |
| Number Filters | Greater than 50000, Top 10, Above Average |
| Date Filters | This Month, Last Quarter, or tick months in the tree |
| By colour | only red (duplicate) cells |
Filters on several columns work together (AND): Region = North and Sales > 50000.
Clear everything: Data → Clear. A funnel icon on a header shows which column is filtered.
6. Totals that follow the filter — SUBTOTAL
SUM adds hidden rows too. Use:
=SUBTOTAL(9, E2:E500) → sum of visible rows only
=SUBTOTAL(3, A2:A500) → count of visible non-blank rows
SUBTOTAL ignores rows hidden by a filter. Use 109 instead of 9 to also ignore rows you hid manually.
7. Copy only the visible rows
Filter → select the result → Alt + ; (select visible cells only) → Copy → Paste. Otherwise hidden rows can get copied too in some cases.
8. Excel 365: SORT and FILTER functions
=SORT(A2:E100, 5, -1) → sorted by column 5, largest first
=FILTER(A2:E100, C2:C100="North")
They create a live sorted/filtered copy that updates with the data. Covered in detail in a later track.
Common mistakes
Sorting one column only. Headers getting sorted into the data (My data has headers unticked). Using SUM on filtered data and reporting the wrong total. Forgetting a filter is on and thinking rows are missing.