You open a sheet and it looks busy in a way that feels productive: months across the top, people down the side, a total row that glows yellow, a note in column R that says “ask Jen,” and three merged cells that hold the real story. It works until you try to filter, join, or export. Then the grid fights you.
This is Part 2 of From spreadsheets to real data. After when a spreadsheet becomes a liability, we get concrete: what a real table looks like in a sheet, why freeform cells create silent bugs, and how tidy layout unlocks pivots, SQL, and sanity.
What you’ll learn
- Headers once, one value per cell, one idea per column
- Tidy (long) vs wide layouts in plain language
- How freeform formatting breaks filters and joins
- A conversion checklist from “pretty report” to “analysis table”
- How this sets up keys and cleaning in later parts
A table is a contract with your future self
In analysis land, a table is not “anything with grid lines.” It is a set of rows that mean the same kind of thing, under columns that mean the same kind of thing every time. That boring discipline is what lets tools help you. Freeform cells are great for communication layouts. They are hostile for computation.
If you have ever broken a pivot by inserting a blank row for “spacing,” you already understand the war between presentation and structure.
Three rules that prevent most pain
1. One header row
Headers sit in exactly one row. No stacked titles above the headers inside the data range. Titles belong above the table or on a dashboard tab, not inside the machine-readable block.
2. One value per cell
Example:
If a cell says “Acme / Beta” you have two facts smashed into one place. Split them. Same for “10 (est.)” when you need a number: put the estimate flag in its own column.
3. One kind of thing per column
A column named amount should not hold “N/A”, “see notes”, and 12.5 depending on the row. Mixed types force every consumer to guess. Prefer blank plus a status column, or a true null convention you document.
Tidy vs wide (same story, different shape)
Wide layouts put repeating measures across columns (Jan, Feb, Mar). They look like reports. Tidy (sometimes called long) layouts put those measures in rows with a month column. They look less like slides and more like databases. Analysis almost always wants tidy first; you can always pivot to wide for a chart later.
| Question | Wide freeform | Tidy table |
|---|---|---|
| Filter to one month | Delete columns carefully | Filter month = Jan |
| Add a month | Insert column, fix formulas | Add rows |
| Join to another file | Painful | Join on keys + month |
| Load to SQL | Often needs reshape | Natural load |
Freeform habits that look harmless
Messy freeform vs tidy columns:
- Merged cells for labels: filters and sorts misbehave.
- Totals inside the table body: sums double-count if you forget to exclude them.
- Color as data: “red means late” is invisible to SQL and to colorblind teammates. Put status in a column.
- Multiple tables on one tab with gaps: the “table” becomes a treasure map.
- Notes in random cells: put notes in a notes column or a separate log tab.
Worked example: monthly headcount
Someone tracks headcount by team across months in a wide grid. Leadership asks for average headcount by team for Q1 and a join to a cost file keyed by team and month. In the wide sheet you rewrite formulas. In a tidy sheet you filter and join.
team,month,headcount
Engineering,2026-01,12
Engineering,2026-02,13
Sales,2026-01,8
Sales,2026-02,9That CSV-shaped layout is not “less professional” than a pretty matrix. It is more honest about grain: one row is one team-month.
Conversion checklist (pretty report to analysis table)
- Copy the data range to a new tab named data_tidy (never destroy the presentation tab first).
- Unmerge everything. Fill down labels that were only in merged header cells.
- Delete blank spacer rows and total rows from the body (or mark them in a row_type column if you must keep them).
- Ensure one header row with unique, boring names: snake_case or clear words without punctuation games.
- Reshape wide months into long form if measures repeat across columns.
- Save a dated snapshot before the next refresh.
How this connects to the series
Part 1 named liability. Part 2 names structure. Next, keys and IDs make structure joinable. Cleaning (Part 4) is easier when columns are consistent. Exports (Part 5) fail less when tables are tables. The table contract (Part 6) writes the rules down.
Language for the meeting
- “Is this a presentation range or an analysis table?”
- “What does one row mean?”
- “Can we keep a tidy tab even if the dashboard tab stays pretty?”
- “Which columns are measures vs categories?”
Personal tool vs multiplayer table
On a personal sandbox, freeform is fine. On a multiplayer source of truth, freeform is a future incident. The cost is paid by whoever inherits the file at 11 p.m. before a board meeting. Structure is kindness.
Practice this week
- Pick one wide sheet you use monthly. Create a tidy copy with a grain statement in row 0 notes or a _meta tab.
- Remove one merged-cell label from an analysis range.
- Replace one “color means status” habit with a status column.
- Export the tidy tab to CSV and open it: if it still makes sense, you did it right.
Common mistakes
- Treating the dashboard layout as the only copy of the data
- Encoding meaning only in formatting
- Leaving totals in the body of a table used for analysis
- Renaming headers casually mid-year without a changelog
More on presentation debt
Presentation debt is the quiet cousin of technical debt. Every merged cell that makes a slide prettier makes a filter harder. Every total row that makes a manager smile becomes a trap for the next analyst who sums a column without noticing. The fix is not to ban presentation. It is to separate the pretty view from the analysis table the same way you separate a chart from its underlying query.
When stakeholders insist the analysis tab must “look like the board pack,” offer two tabs with a written rule: the tidy tab is canonical; the pretty tab is derived. If someone edits the pretty tab only, treat that as a process bug. This single social rule prevents more wrong numbers than a dozen clever formulas.
Also watch for “analysis by screenshot.” If the only durable artifact is a PNG of a pivot, you do not have a table. You have a moment. Moments do not reconcile next quarter. Keep the tidy rows that produced the screenshot, with a date and owner, even if nobody asks for them yet.
Teams that win long term make structure the default and prettiness the optional layer. That is slower for the first hour and faster for the next year. Part 1 called the alternative a liability. Part 2 is the first concrete climb down: make the data rectangular, boring, and honest about grain.
If you need a heuristic: if a new teammate cannot describe one row in one breath, the sheet is still freeform theater. Fix that before you build another dashboard on top of it. Structure first. Charts second. Arguments third, and rarer.
Wide reports still have a place
Wide matrices are excellent for human scanning of small time series. Finance packs often need months across columns. The error is using that matrix as the only stored form of truth. Generate wide views from tidy facts when you need them. Store tidy facts when you must compute, join, or reload.
In Sheets you can keep both: a data_tidy tab fed by imports, and a report tab that uses QUERY, pivot tables, or array formulas to rewiden for display. The report can break; the tidy tab should still be reloadable. That asymmetry is intentional and healthy.
Quick recap
Real tables have one header row, one value per cell, and consistent columns. Tidy layouts store repeating measures as rows, which tools love. Freeform presentation is fine for humans reading slides; analysis needs structure. Convert presentation ranges into tidy tabs before you join, clean, or export.
Next: Keys, IDs, and joining in plain English. Series home: From spreadsheets to real data.
Sources
- Wickham, Hadley. “Tidy Data.” Journal of Statistical Software (concepts of tidy tables; readable summary widely available): https://doi.org/10.18637/jss.v059.i10
- Analytics Made Simple series: From spreadsheets to real data; Analytics foundations on grain and definitions
Why “it works in my pivot” is not enough
Pivots can paper over messy structure until they cannot. A layout that only works because you memorized which rows to exclude is not a table; it is a ritual. Rituals do not survive vacations, handoffs, or SQL. If your process requires a human to remember secret blank rows, you are one absence away from a wrong total.
Invest in boring structure while the file is still small. The longer you wait, the more presentation debt compounds. Part 1 called that liability. Part 2 is how you stop digging.
Teams that keep a tidy tab alongside a pretty tab ship fewer midnight rescues. Pretty tabs communicate. Tidy tabs compute. You need both, with a clear boss: the tidy tab owns truth; the pretty tab is a view.
