TABLES COURSE · Step 9 of 20

Use named columns and Table sections

Lesson instructions

Use the complete current exercise check before saving. Navigation does not record a pass.

Before this step: Create a named Table and distinguish inputs from calculated cells.

Inside a Table, @ selects the current row. Outside it, include the Table name. Header, body, total and all-section references select different cells, so the same aggregation can produce a different answer.

Inspect the read-only steps on the actual Orders worksheet, including the current-row formula outside the Table. Switch to Practice on that same sheet.
Keep a source-dependent column, totals formula and every summary correct.
Make an actual source or formula change, then check the complete current and retained source cases before saving.

Original source

Table structure and worked formulas

Use table names instead of fixed row endpoints

The complete source is named Orders. Its 12 data rows occupy A2:G13; A1:G14 also includes the header and physical totals row. Edit rows or display headers below while retaining your applied formulas. This complete source is independent of report filters.

=IF([@Status]="Paid",[@Amount],0) calculates the qualified amount in each data row. =SUBTOTAL(109,Orders[Paid amount]) totals that column. Column references include data rows by default; #Headers, #Totals and #All select different sections.

Teaching · read-only · Report!A1

Step 1 / 8Read the header, records and physical total

Inspect

Orders includes one header, every complete record and a physical totals row. A body-column reference selects only its data cells. Teaching is read-only; switch to Your turn to edit.

Source!A1 · (blank)
No formula applied

Loading your interactive spreadsheet…

Use another complete source

Keep formulas while editing the table

These edits use the current worksheet records, applied formulas and literal query. The seven role columns stay in place; their display names can change. Import records into the report explicitly when ready.

Row position

New data recordidmonthregionproductstatusamount

Role columnNew display header

Row edits keep 1–50 records and one shared structured column formula. Direct A1 references into table data/totals and unresolved selectors need correction first. Renaming updates bound column selectors; quoted formula text stays literal. Undo is withheld after later source or formula changes.

Formula targetFormula draft

Drafts are applied only by Apply formula. The calculated column uses one shared formula; applying it fills every data row. Check also assesses the totals row and all eight summary slots on 7 retained source cases. The region summary compares exact text, including wildcard-looking and numeric-looking labels.

Literal region queryApplied J11: null

Static values only

The native download includes the Orders table, calculated-column metadata, a physical totals row and current formulas/caches. Static mode removes formulas. Native Excel filtering, automatic expansion and edit/save/recalculation remain unverified.

Microsoft: structured Table references ↗