2.5Sort, filters & structured tables
The essentials in 30 seconds
- Filtering hides, sorting orders: the filter hides temporarily, multi-level sorting orders — neither deletes.
- Ctrl+L = pro reflex: range converted into a named, filterable, extensible table — references by name.
- No more frozen ranges: =SUM(D2:D200) forgets the 201st row — =SUM(Ledger[Amount]) follows everything.
Running mini-case — Wassim and the total that lied
Wassim (2.4) has a luminous table, but his total =SUM(D2:D200) ignores the month's 201st invoice: the report goes out wrong to the boss. He converts his range into a structured table (Ctrl+L), names it Ledger: filterable headers, readable bands, and above all references by name — =SUM(Ledger[Amount]) includes each new row automatically. His 2.2 XLOOKUPs now point to named columns, his 2.4 rules extend by themselves. Wassim's level 2 is wrapped up: decide, look up, clean, alert, structure.
Epilogue of the 2.1-2.4 thread: Wassim's tracker becomes a table that grows without lying — ready for the level 3 pivot tables.
1. Filters: seeing a subset
Filters temporarily hide the rows outside the criterion (a wilaya, an amount > 100000, a month): the data is not deleted, just hidden. You combine several columns to refine. Multi-level sorting orders (by supplier, then by amount descending). Wassim filters his Algiers unpaid items sorted by delay descending: his priorities for the day in two clicks.
2. The structured table: the pro reflex
Converting a range into a structured table (Insert ▸ Table, or Ctrl+L) brings: frozen filterable headers, readable bands, automatic extension (a new row inherits the formulas and the 2.4 rules), and above all references by name: =SUM(Ledger[Amount]) instead of =SUM(D2:D200). Wassim names his table Ledger and his formulas become readable for his boss.
3. Why it is safer
A structured table grows by itself: add a row and formulas, rules and future pivots take it into account. Calculated columns propagate automatically (the 2.3 delay formula goes down by itself). No more frozen ranges that «forget» new rows — the #1 cause of wrong reports. Wassim adds his 201st invoice: the total follows, without retyping any formula.
🔎 Concrete example — 🔎 Concrete example — The total that follows everything
On a frozen range vs Wassim's Ledger table:
Frozen range: =SUM(D2:D200) → the 201st invoice is FORGOTTEN
Table «Ledger»: =SUM(Ledger[Amount]) → includes ANY new row
Named lookup: =XLOOKUP([@Code], VATRates[Code], VATRates[Rate], "To check")Reading: the reference by name covers the whole table column, whatever its size; [@Code] designates the current row.
Lesson: converting to a table removes the «too short» range errors, the #1 cause of wrong totals.
Section vocabulary
- Filter
- Temporarily hides the rows outside the criterion; the data is kept and comes back when removed.
Simple example: Wassim filters his Algiers unpaid items without deleting anything.
What it is not: a definitive deletion of rows.
Reflex: filtering = seeing, not deleting.
- Multi-level sort
- Orders according to several successive columns (supplier, then amount descending).
Simple example: Wassim sorts by wilaya then by delay descending.
What it is not: a filter that hides rows.
Reflex: order from the general to the precise.
- Structured table
- Range converted (Ctrl+L): named, filterable, extensible, with automatic calculated columns.
Simple example: Wassim converts his tracker and names it Ledger.
What it is not: a simple range colored by hand.
Reflex: Ctrl+L as soon as it must last.
- Structured reference
- A column designated by its name (Ledger[Amount]) instead of a frozen range: follows the additions.
Simple example: Wassim totals with =SUM(Ledger[Amount]).
What it is not: a frozen range like D2:D200.
Reflex: name rather than freeze.
In practice — In practice — Structuring your tracker
- Convert a range of invoices into a table (Ctrl+L) and name it «Ledger».
- Filter by status and sort by amount descending.
- Write =SUM(Ledger[Amount]) and add a row: check that it is included.
- Create a calculated column (delay = due date − today) and watch it propagate.
Ready-to-say script — explaining the table to the boss (30 s)
« The tracker has become a real table named Ledger: I filter by status, I sort by amount, and the total automatically follows each new invoice — no more forgotten range. The formulas are read by column name, and the delay column fills itself. »
Say it while adding a row in front of the boss and showing the total follow.
Copy-paste template — structured total
=SUM(Ledger[Amount])
=XLOOKUP([@Code], VATRates[Code], VATRates[Rate], "To check")
=[@DueDate]-TODAY()Wrong total
The 201st invoice is missing from the total: Wassim converts to a table and switches to Ledger[Amount] — no more frozen range ever.
Today's priorities
Isolating the Algiers unpaid items by delay: Wassim combines a wilaya + status filter and a descending sort on the delay.
Self-filling column
Copying the delay to each row: Wassim makes it a calculated column of the table, propagated automatically.
Stray empty rows
The table cuts itself in two: Wassim deletes the empty rows and columns that break the structure.
✓ Best practices
- Convert to a table (Ctrl+L) as soon as data must live.
- Name the table (Ledger, VATRates…) and use references by name.
- Filter to see, sort to order — never to delete.
✗ Common pitfalls
- Formulas on frozen ranges (D2:D200) that forget the new rows.
- Filtering believing you delete (the rows are just hidden).
- Leaving empty rows or columns that cut the table.
Key takeaways
- Filter = hide temporarily; sort = order (multi-level).
- Ctrl+L converts a range into a named structured table.
- References by name: Ledger[Amount] (robust to additions).
- The table extends by itself: formulas, rules and calculated columns follow.
48h challenge — apply tomorrow
Convert your real tracker: Ctrl+L + name, a useful filter + sort, a total by name checked after adding a row, and a propagated calculated column.
- I can filter and sort (multi-level) without losing or deleting data.
- I can convert to a structured table (Ctrl+L) and name it.
- I use references by name and calculated columns that follow the additions.
Frequently asked questions
Does filtering delete data?
No: the filter hides the rows outside the criterion, they come back when removed. To actually extract a subset, copy the visible rows elsewhere or use the FILTER function (advanced level).
Why are my new rows not in the total?
Your formula points to a frozen range (D2:D200). Convert the source into a structured table (Ctrl+L) and use =SUM(Ledger[Amount]): it extends automatically to the new rows.
Why convert a range into a structured table (Ctrl+L)?