The first time you write a formula inside a proper Table, something unexpected happens. You click a cell to reference it, and instead of C2, Excel writes:

=[@Amount] * [@Rate]

Some people’s instinct is to flinch and turn it off. Don’t — you’ve just met structured references, the grammar Tables speak, and it’s the most readable dialect Excel has. This lesson is that grammar, on one page.

The grammar, on one page

Given a Table named Orders (you did name it — top-left of the ribbon under Table Design; Table1 is a wasted gift):

You write It means
[@Amount] Amount, this row
Orders[Amount] the whole Amount column
Orders[#Totals] the totals row
Orders[#Headers] the header row
Orders[] the whole data body

The @ is the heart of it: “this row”. =[@Amount]*[@Rate] reads as this row’s amount times this row’s rate — no dollar-sign choreography, no counting columns, nothing to re-aim when the Table moves or grows. From outside the Table, the name does the pointing: =SUM(Orders[Amount]) from any sheet in the workbook, and it reads like a sentence — the same kindness as named ranges, handed to you free with every column.

The two behaviours that change your work

Formulas fill themselves. Type a formula in one cell of a Table column and it instantly fills the whole column — and every row added later inherits it. This is the calculated column, and it retires the fill handle for Table work. One formula per column, everywhere, always — which also means one truth per column: if you find yourself overwriting a few cells of a calculated column by hand, the column wants an IF, not exceptions. (A little dropdown appears after auto-fill if you genuinely want it off, once.)

References stretch. =SUM(Orders[Amount]) is immune to the oldest bug in spreadsheets — the summary formula frozen at B2:B500 while the data quietly reaches row 501. The column reference is the column, today’s size, always.

Item Amount Inc VAT Widgets 200 240 Gaskets 100 120 Flanges ← new row 50 60 ← auto-filled elsewhere: =SUM(Orders[Inc VAT]) = 420 ← stretched by itself no formula edited, no range re-pointed
Add a row: the calculated column writes its own formula, and every reference to the Table already includes it. This is the bug class Tables deleted.

The idioms and the honest wrinkles

The everyday combinations: SUMIFS over Table columns (=SUMIFS(Orders[Amount], Orders[Region], "North")) — criteria formulas that survive growth; XLOOKUP between Tables (=XLOOKUP([@SKU], Prices[SKU], Prices[GBP])) — a join that reads like English; and spilled functions pointed at Tables (=UNIQUE(Orders[Region])) — the two features were made for each other, with the one rule that spills must land outside Tables.

The wrinkles, honestly. A structured reference doesn’t behave like $B$2 when dragged sideways — [@Amount] shifts to the next column, which surprises people building two-way grids; for row-by-row Table work it’s simply not an issue. Spaces in column names force bracket-heavy syntax ([@[Unit Price]]) — name columns UnitPrice and the noise disappears. And if the grammar ever produces something baffling, remember it’s still the grid underneath: Orders[Amount] is just a range that knows its own name and size.

The deeper shift is the one running through the whole of the path: every stage replaces coordinates with meaning — names over addresses, LET-named steps over nested mystery. Structured references are where that shift becomes the default rather than a discipline: build on Tables and your formulas document themselves, whether you were trying or not.

=[@Amount]*[@Rate]. Say it out loud. That’s what formulas were supposed to sound like all along.