`$` and what the fill handle does to each

Three kinds of reference

When the fill handle copies a formula one row down, every relative reference in it moves one row down with it: D2 becomes D3. That is what makes filling a column useful — =D2/E2 in row 2 becomes =D3/E3 in row 3, each order divided by its own units. But some references must not move. A dollar sign pins a part of the address: $D$14 is absolute — neither the column nor the row changes when filled in any direction. D$14 is mixed, row locked: the column slides on a fill across, the row 14 never changes. $D14 is the other mixed form, column locked. The rule to say aloud: the $ locks whatever it stands in front of. In both Sheets and Excel, pressing F4 while the cursor is on a reference cycles it through D14 → $D$14 → D$14 → $D14 → D14.

Worked example — share of total, and the cascade

In the orders sheet put the grand total in D14: =SUM(D2:D13) → 6140. In G2 type =D2/D14 for order 1001's share: 420 / 6140 = 0.0684. Fill it down to G13. Row 2 is right. Row 3 shows #DIV/0!. So do rows 4 through 13.

The formula in G3 is now =D3/D15. The numerator moved as intended; the denominator moved too, from D14 onto the blank D15, and a blank divides as zero. Row 4 points at D16, row 5 at D17, all blank, all #DIV/0!. Nothing is wrong with the data and nothing is wrong with the total — the reference slid.

The fix is one edit in G2: =D2/$D$14. Fill down again and every row divides by 6140: 0.0684, 0.0293, 0.1238 for orders 1001, 1002, 1003. A second form avoids the helper cell altogether: =D2/SUM($D$2:$D$13) — the range inside SUM is absolute, so each copy sums the same twelve cells. Both are correct; the second is the pattern COUNTIF will use in Module 4.

A mixed reference, for a two-way table

Maple & Birch's price list has unit prices down column A — Ledger binder 24, Desk lamp 60, Office chair 180 in A2:A4 — and order quantities across row 1 — 1, 5, 10 in B1:D1. One formula in B2 must fill both down and across: =$A2\ast B$1. Filled down, $A2 keeps column A and the row follows; filled across, B$1 keeps row 1 and the column follows. D4 becomes =$A4\ast D$1 = 180 × 10 = 1800. An absolute $A$2 would price everything as a binder; a relative A2 would drift off the price column on the first fill across.

Read the error, then type the fix

A #DIV/0! that appears from row 3 after a fill is a slid denominator, nearly every time: the data does not contain zeros and the total is not missing — the reference moved. Type the two fixed forms below, then compute order 1003's share of the quarter as a percentage.

CRISP-DM: a share-of-total column is data preparation → construct data; recognising the cascade is verify data quality applied to your own work.