📎 This article includes 1 downloadable practice file ↓
In this article
A list of 5,000 orders is useless until you can ask it questions: which were the biggest, what did North sell, which came in last week. Sorting puts rows in order; filtering hides the rows you don’t want to see. Both are on the Data tab, and both are safe once you know the one rule below.
The one rule: let Excel select the whole table
Click a single cell inside your data, then sort. Excel detects the whole block and keeps each row together. If you select just one column and sort it, Excel asks whether to expand the selection. Always choose Expand the selection; otherwise that column is sorted on its own and every row is scrambled.
Quick sort
Click any cell in the column, then Data › A→Z (smallest to largest, oldest to newest) or Z→A. Done.
Sort by several columns
Data › Sort opens the dialog. Add levels:
- Sort by Region, A to Z
- Then by Amount, Largest to Smallest
Result: regions grouped together, biggest orders first within each. Make sure My data has headers is ticked so the header row stays on top.
Custom order
Months sort alphabetically (Apr, Aug, Dec…) unless they are real dates. For text like Jan–Dec, or your own order (High, Medium, Low), choose Order › Custom List in the Sort dialog and pick or type the list.
Filter: show only what you need
Click inside the data and press Ctrl + Shift + L (or Data › Filter). Drop-down arrows appear on the headers.
| Column type | Filter options |
|---|---|
| Text | Tick values, or Text Filters › Contains / Begins with (e.g. names containing “Traders”) |
| Numbers | Number Filters › Greater Than, Between, Top 10 |
| Dates | Grouped by year and month; Date Filters › This Month, Last Quarter, Between |
| Any | Filter by Color if cells or fonts are coloured |
Filters on several columns combine as AND: Region = South and Product = Laptop. The row numbers turn blue and the filtered column’s arrow shows a funnel.
Totals of filtered rows
With a filter on, select the Amount column: the status bar shows the total of visible rows only. A normal SUM below the data still adds hidden rows too. Use SUBTOTAL for a filter-aware total:
=SUBTOTAL(9, G2:G41) ' 9 = SUM of visible rows only
=SUBTOTAL(3, A2:A41) ' 3 = COUNTA: how many rows are showingClear filters
Data › Clear removes all filter criteria; Ctrl + Shift + L again removes the arrows entirely. Before sending a file, clear filters, or the reader may think rows are missing.
Formula versions (Excel 365)
SORT and FILTER give the same results without touching the original data, and they update automatically:
=SORT(A2:G41, 7, -1) ' all orders, biggest amount first
=FILTER(A2:G41, C2:C41="North") ' only NorthMore in dynamic arrays: SORT, FILTER and UNIQUE.
Where beginners go wrong
| Mistake | Result |
|---|---|
| Sorting one selected column | Rows scrambled; press Ctrl + Z immediately |
| Header row sorted into the data | Tick “My data has headers” |
| Dates stored as text | They sort alphabetically: 01-Aug before 02-Jul |
| SUM under a filtered list | Shows the total of everything; use SUBTOTAL |
| Forgetting a filter is on | “Missing” rows; look for blue row numbers |
Practice
Download this lesson’s workbook below. The Orders sheet has 40 orders; do the sorts and filters by hand, then answer the questions. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 7 practice workbook40 orders to sort and filter by region, product, amount, date and customer, with questions to check your results.⬇ XLSX · 16 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.