A stable source, and checking your own formulas

A range with a name and a memory

A plain range like A1:F13 is just cells; nothing about it knows that row 1 is a header or that a thirteenth order belongs to the same data. An Excel Table does. Select any cell in the orders data and press Ctrl+T (Insert → Table, My table has headers ticked). Excel bands the rows, adds filter buttons to the header, and gives the table a name — rename it Orders in the Table Design tab. From then on three things hold. New rows typed directly under the table join it automatically, so every formula and pivot built on the table sees them. Columns can be referred to by name — a structured reference — instead of by address. And a formula typed once in a new column becomes a calculated column that fills every row of the table by itself, new rows included.

Worked example — two references and a spot check

With the table named Orders, the quarter's total is =SUM(Orders[amount]) — 6140, the same as =SUM(D2:D13), but it still reads correctly after a hundred more orders arrive. Add a column headed unit_price and, in its first cell, type =[@amount]/[@units]. The @ means this row: amount divided by units for the same order. Press Enter and the whole column fills — 140, 180, 152, 125, 165, 127.5, 90, 160, 152.5, 163.33, 107.5, 156.67. That is the derived variable from Module 5 built the safe way: one formula, no fill handle, no reference to slide.

Now audit it. Press Ctrl+** (the backtick, top-left of the keyboard) and every cell shows its formula instead of its value — twelve copies of =[@amount]/[@units], which is what a calculated column should look like; a plain range filled by hand would show =D2/E2, =D3/E3, … and any row where somebody had typed a number over the formula would stand out at once. Press Ctrl+ again to return to values. Then spot-check one row by hand: order 1001 is $420 for 3 units, and 420 / 3 = 140**, which is what the cell shows. One row, thirty seconds, and the whole column is trusted — because if the formula is right for one row of a calculated column it is right for all of them.

What a Table replaces

Before Tables, analysts put a total row inside the range — a bold Total under the last order — and it poisoned everything: a sort scattered it among the orders, a pivot counted it as data, a SUM over the column double-counted (Lesson 2.1). A Table's optional Total Row (Table Design → Total Row) lives outside the data: sorts, filters and pivots ignore it. A range with an unlabelled total row is a data-quality fault; a labelled one is the same fault with a name.

Type the references, then name the task

Type the two structured references below. Then answer: when you spot-check a derived column against one row computed by hand, which CRISP-DM task are you performing? Verify data quality — the data-understanding task — applied to a column you built yourself. It is not modelling; nothing has been modelled. Your own output is data, and it deserves the same check as anyone else's.

CRISP-DM: converting the range to a Table is data preparation → format data; the calculated column is construct data; the spot check is data understanding → verify data quality.