Addresses, ranges, and the fill handle

The grid has an address for everything

A workbook (Excel) or spreadsheet (Google Sheets) is a file; it holds one or more sheets; each sheet is a grid of cells. A cell's address is its column letter followed by its row number — D2 is column D, row 2. In the Maple & Birch orders sheet, row 1 is the header row (order_id, prov, channel, amount, units, order_date) and rows 2 to 13 hold the twelve orders, so D2 is order 1001's amount, 420, and B13 is order 1012's province, BC.

A range is a rectangle of cells named by two opposite corners joined by a colon: D2:D13 is the twelve amounts, A1:F13 is the whole table including its header, A2:F2 is one order across. The colon is the range operator — it means every cell from here to there. A comma means something different: D2,D13 is two separate cells, the first and the last amount, and nothing in between. =SUM(D2:D13) returns 6140; =SUM(D2,D13) returns 420 + 470 = 890. Same function, one character apart, a difference of 5,250.

A whole-column reference drops the row numbers: D:D is every cell in column D, header included. It is convenient because it never needs extending when orders are added, and safe for SUM because the header text is ignored. Excel requires exactly this form; Sheets also accepts an open-ended D2:D, meaning from D2 down to the bottom, which Excel rejects.

Worked example — setting up the orders sheet

Open the export in Sheets. Click cell A1 and drag to F1: that is the header. Click D2, hold Shift, click D13: the name box now reads D2:D13, the amount column without its header. Two habits make the sheet usable at any length. Freeze row 1 (View → Freeze → 1 row) so the headers stay visible while you scroll. Then, in G1, type line and in G2 type 1; select G2, and drag the small square at its bottom-right corner — the fill handle — down to G13. Sheets fills a series 1, 2, …, 12. Drag a formula the same way and the fill handle copies the formula, adjusting its references row by row — which is exactly what Lesson 3.3 is about.

Sheets and Excel, one vocabulary

The first half of this course uses Google Sheets; pivots and dashboards move to Excel. The core function names are identical in both — SUM, AVERAGE, COUNTIF, VLOOKUP, ROUND — and a quiz may name either tool. What differs is at the edges: Sheets has QUERY, SPLIT and always-on FILTER, UNIQUE and SORT; Excel has Tables, the Analysis ToolPak and richer pivots. When a lesson uses a Sheets-only function it says so.

Two ranges, then one question

Type the two SUM forms below, then decide: the crew wants the total of all twelve amounts — which of =SUM(D2:D13) and =SUM(D2,D13) does it? Only the colon form. The comma form is a legitimate formula that answers a question nobody asked, and Sheets will not warn you.

CRISP-DM: naming the range that holds each variable is data understanding → describe data.