How Data Must Sit in a Spreadsheet

The layout is half the analysis

Almost every statistics problem that looks hard in Excel is a layout problem in disguise. A PivotTable that refuses to group, a formula you rewrite for every month, a chart whose series you reorder by hand — these are rarely limits of Excel. They are what a spreadsheet does when data is stored in the shape a human likes to read rather than the shape a machine can compute on. By the end of this lesson you will have a rule you can apply to any file that lands on your desk.

Three rules, and everything follows from them

• One row is one observation. One sale, one visit, one measurement, one day in one shop. If a row describes two things, no formula can count them apart.
• One column is one variable. Every value in it is the same kind of thing, in the same unit. A column called Amount holding francs in some rows and euros in others is two variables sharing a name.
• One cell is one value. Zurich (ZH) 1'250 CHF is three values wearing one coat: city, canton code, amount. Split them at import, not later.
• One table per sheet, headers in row 1, no blank rows, no totals among the observations, and no colour used to carry meaning — a fill colour is invisible to every formula you will ever write.

The same numbers, stored two ways

Here is the layout most offices produce, because it is the one that prints nicely:

Canton    Jan    Feb    Mar
Zurich    120    143    131
Bern       88     95    102
Vaud       74     69     80

It reads beautifully and computes badly. The variable Month is stored nowhere — it hides in the header row, so no formula can filter or group by it, and adding April means widening every range and every chart. The tidy version holds the same twelve numbers like this:

Canton    Month    Units
Zurich    Jan      120
Zurich    Feb      143
Zurich    Mar      131
Bern      Jan       88
Bern      Feb       95
Bern      Mar      102
Vaud      Jan       74
Vaud      Feb       69
Vaud      Mar       80

Taller, duller, and worth it: month is now a real variable, so it can be filtered, grouped, averaged and charted. Adding April means appending rows, and nothing downstream changes.

Prove it to yourself in sixty seconds

Type the tidy block into a blank sheet with headers in row 1, then enter these four formulas anywhere. Each answers a different question, and each keeps working as the table grows:

=SUMIF(A2:A10,"Zurich",C2:C10)      ' total units for one canton
=AVERAGEIF(B2:B10,"Feb",C2:C10)     ' average across cantons in one month
=COUNTIF(A2:A10,"Bern")             ' how many observations that canton has
=MAX(C2:C10)                         ' the largest single observation

Now try to write the first of those against the wide layout. You cannot, not generally: you would need =SUM(B2:D2), which depends on Zurich sitting in row 2 and breaks the moment somebody sorts the sheet. That gap — a formula that survives sorting versus one that does not — is the whole argument for tidy data.

The real trap: the total row that lives inside the data

The most common way a good table is ruined is a Total row placed directly under the last observation, or a subtotal after each canton. To your eye it is obviously a summary. To AVERAGE, COUNT and every method in the rest of this course it is one more observation — roughly the size of all the others put together, so it drags the mean up and inflates the spread. Blank spacer rows do quieter damage: Ctrl+Shift+End and a PivotTable's automatic range stop at the first empty row, so part of your data silently leaves the analysis. Keep totals above the headers or on another sheet.

Where this goes next

The rest of this course fixes what layout alone does not. What Excel Actually Stores shows why a cell that looks numeric may not be, and how one formula detects it. Numbers Stored as Text repairs those cells. The Date Trap covers serial numbers and the day/month ambiguity. Blank, Zero and NA shows how differently each is treated by every average you compute. Excel Tables makes ranges grow by themselves, and Keys, Duplicates and Lookups joins two files without inventing rows. The last lesson builds the validation panel you run over any new file.