🟡 Intermediate 20 minintermediate path

Intermediate Excel: Formulas, Cell References & Tables

Intermediate Excel is where the real productivity lives: writing formulas that update themselves, using absolute references so copies don't break, sorting and filtering large lists, and turning ranges into Excel Tables for clean, expandable data.

Learning objectives

Write formulas using relative, absolute, and mixed references.
Sort and filter a data range without breaking the rows.
Convert a range to an Excel Table and use structured references.
Set page layout and print titles so multi-page reports look professional.

Step-by-step

1. Relative vs. absolute references

=B2*C2 copied down becomes =B3*C3, =B4*C4, and so on — that's a relative reference. Add dollar signs to lock: =$B$2 always points at B2. =$B2 locks the column, =B$2 locks the row. F4 toggles the modes.

2. Sorting safely

Click any cell inside the list, Data → Sort. Excel expands the selection to the whole contiguous range so rows stay together. Never sort just one column — you'll scramble every other column.

3. Filtering

Data → Filter adds dropdown arrows. Use them to show only rows that match — one region, one date range, invoices above a threshold. The row numbers turn blue to remind you rows are hidden.

4. Excel Tables

Select your data, Ctrl+T, confirm headers. You get banded rows, auto-filter, a total row, and structured references like =SUM(Sales[Amount]) that automatically grow when you add rows.

5. Page layout for printing

Page Layout tab → Print Titles → Rows to repeat at top (usually $1:$1). Use Page Break Preview to see how the sheet will paginate before you hit print.