Sorting, Filtering & Excel Tables

Excel Fundamentals

Chapter 7 · Sorting, Filtering & Excel Tables

Every chapter so far has treated a block of data as a fixed shape — a known range with a known number of rows. Real data doesn't stay still: it needs reordering, it needs narrowing down to just what matters right now, and it keeps growing as new rows get added. This chapter covers Excel's tools for all three, ending with the single upgrade that fixes the most annoying limitation of a plain range once and for all: the Excel Table.

Sorting Data

Selecting a single column and clicking the A→Z or Z→A buttons (Home → Sort & Filter) sorts by that column alone. For anything more nuanced, the full Sort dialog (Data → Sort) supports sorting by multiple columns in priority order — for example, sort by "Department" first, and within each department, sort by "Last Name." Each level can independently be ascending, descending, or even sorted by cell colour or icon rather than the value itself.

Sorting only part of a table silently breaks each row's data

If you select just one column and sort it while its neighbouring columns stay in their original order, Excel warns you the first time — but selecting the wrong option (or a header-less range that doesn't trigger the warning) will happily shuffle names in one column while leaving their matching scores in another column completely unmoved, permanently scrambling which value belonged to which row. Always select the FULL data range (or better, use an Excel Table, covered below, which makes this mistake structurally impossible).

Filtering Data with AutoFilter

Select your data and turn on Filter (Data → Filter, or Ctrl+Shift+L) to add a small dropdown arrow to every column header. Clicking one lets you show only rows matching specific values, a text pattern ("contains," "begins with"), or a numeric condition ("greater than," "between"). Filters set on multiple columns combine with AND logic — a row must satisfy every active filter simultaneously to remain visible.

Filtering never deletes anything — it only hides rows that don't match. The hidden rows are still there, still counted by some functions and skipped by others, which is exactly the distinction covered later in this chapter's warning box about SUM versus SUBTOTAL.

The Problem With a Plain Range

Suppose A1:D50 holds a data set, and a formula elsewhere references =SUM(D2:D50). The moment row 51 is added with new data, that formula does not automatically include it — D2:D50 is a fixed address, not a description of "wherever the data currently ends." Every formula, every filter, every chart built on that range needs manually re-extending to ...D51, and it's easy to forget one.

Excel Tables: Data That Knows Its Own Boundaries

Select your data (including headers) and press Ctrl+T (or Insert → Table) to convert a plain range into an Excel Table — a genuinely different kind of object, not just a colour scheme. A Table:

Expands automatically. Typing data into the row immediately below the Table's last row instantly becomes part of the Table — no re-selecting a range required.
Gets filter dropdowns automatically on every header the moment it's created — no separate step needed.
Applies banded row styling automatically, and updates it automatically as rows are added, removed, sorted, or filtered.
Can show a Total Row (Table Design → Total Row) — a dropdown per column offering SUM, AVERAGE, COUNT, MAX, MIN and more, without writing a single formula by hand.
Extends any formula referencing it automatically — a chart, a PivotTable, or a formula built from a Table's own structured references (below) all grow with the Table, with zero manual maintenance.

Structured References: Naming Columns Instead of Counting Them

Once a range becomes a Table (Excel names it something like Table1 by default, renamable in Table Design), formulas can refer to its columns by their actual header name instead of a cell address:

Plain-range version — breaks the moment a row is added past row 50:
=SUM(D2:D50)

Structured reference version — always means "every value currently
in the Sales column of Table1," no matter how many rows exist:
=SUM(Table1[Sales])

Inside a calculated column, [@ColumnName] means "this same row, that column":
=Table1[@Price] * Table1[@Quantity]

Table1[Sales] refers to the entire Sales column, whatever its current length. Table1[@Sales] (note the @) means "the Sales value in this exact same row" — used inside a formula that lives inside the Table itself, calculating one row at a time. Type such a formula into one cell of a Table column, and it automatically fills down to every other row in that column on its own.

Behaviour	Plain range	Excel Table
New row added at the bottom	Formulas/filters need manual re-extending	Table, formulas, and filters all expand automatically
Referencing a column in a formula	A1-style address, e.g. D2:D50	Named structured reference, e.g. Table1[Sales]
Filter dropdowns on headers	Manually enabled (Ctrl+Shift+L)	Added automatically on creation
Row banding / style	Manually applied, doesn't self-maintain	Automatic, and self-maintains as rows change
Column-total row	Manually typed formula	Total Row — a dropdown per column, no formula typed by hand

Structured references make most of Chapter 3's $ locking unnecessary inside a Table

Table1[@Price] always means "this row's Price," full stop — there's no relative-vs-absolute ambiguity to manage, because a structured reference isn't a counted row/column offset at all. Chapter 3's $ locking is still essential for plain-range formulas (and for references reaching outside the Table entirely, like that fixed tax-rate example), but formulas that stay entirely inside a Table's own columns rarely need a single dollar sign.

SUM counts filtered-out rows; the Total Row's own SUBTOTAL doesn't

Applying a filter that hides some rows does NOT stop a plain =SUM(...) formula from including those hidden rows' values in its total — SUM has no idea some rows are currently filtered out of view. The Table's own Total Row uses SUBTOTAL instead, a function specifically built to skip rows hidden by a filter, which is exactly why the Total Row's number changes when you filter, while a separate hand-written SUM formula elsewhere on the same data would not.

Hands-On Exercises

Exercise 1

A range A1:C20 holds Name, Department, and SalesTotal, one row per employee. Describe the exact Sort dialog setup needed to sort the data so that it's grouped by Department alphabetically, and within each department, employees are ordered by SalesTotal from highest to lowest.

📄 View solution

Exercise 2

A1:C50 holds Product, Category, and Price. Convert this range into an Excel Table named SalesTable. Write a structured-reference formula that calculates the average price of everything in the table, and explain what happens to that formula's result — with no editing required — if 10 new rows of products are added directly below the table.

📄 View solution

Exercise 3

An Excel Table named OrdersTable has columns Quantity and UnitPrice. Write a structured-reference formula for a new calculated column, LineTotal, that multiplies each row's own Quantity by its own UnitPrice. Then explain what happens automatically once this formula is entered into just the first data row of the LineTotal column.

📄 View solution

Chapter 7 Quick Reference

Sort dialog (Data → Sort) supports multi-level sorting; always select the full data range, never one column in isolation
AutoFilter (Ctrl+Shift+L) hides non-matching rows; multiple column filters combine with AND logic
Ctrl+T converts a range into an Excel Table — auto-expanding, auto-filtered, auto-styled
Table1[ColumnName] — the whole column, any length; Table1[@ColumnName] — this row's value in that column
A Table's Total Row uses SUBTOTAL, which skips filtered-out rows — unlike a plain SUM formula, which doesn't
Structured references need little to no $ locking — they aren't counted row/column offsets in the first place