Organize a useful table

Separate raw data, inputs, calculations and outputs into named regions of a worksheet, and give every column one data type and one unit, with the units and the as-of date recorded beside the numbers.

Most spreadsheet disasters are not arithmetic mistakes. They are layout mistakes that arithmetic then faithfully carries forward.

A column that holds 45 in one row and 1.5 in the next is not a column of durations. It is two columns of different things sharing a heading, and every total built on it is wrong by an amount nobody can estimate. A rate typed into eleven formulas is not an assumption; it is eleven assumptions that will disagree the first time one of them is updated. A number with no as-of date cannot be compared with anything.

The fix is unglamorous and takes about ten minutes: decide what each region of the sheet is for, and keep it to that.

A worksheet that other people can check has four kinds of content, and keeps them apart.

Raw data is what arrived. It is not edited, ever. If it came from an export, keep the export on its own sheet and work on a copy. When somebody asks six weeks later why a figure changed, the untouched copy is the only thing that can answer.

Inputs are the assumptions: a rate, a target, a threshold, the date the data was pulled. Each one lives in exactly one cell, with a label next to it. A reader should be able to find every number the sheet assumes without opening a single formula.

Calculations derive new values from raw data and inputs. A calculation cell contains a formula. If it contains a typed-in number, it is not a calculation; it is an input pretending to be one, and it will not move when the thing it depends on moves.

Outputs are the few figures the sheet exists to produce, plus the checks that say whether they can be trusted.

Within the data itself, three rules do most of the work.

One header row. Formulas and sorting both assume the first row of the contiguous table range names the columns and every row below it in that range is a record. The Source export here starts its table at row 5, below a separate title block. Two header rows, a merged title, or a blank row in the middle all break that assumption quietly.

One data type per column. A column is numbers, or it is text, or it is dates. A number stored as text can look identical on screen; SUM skips it and COUNT does not count it. In this grid, as in most spreadsheets, text sits against the left of its cell and numbers sit against the right; that difference in alignment is often the only visible clue.

One unit per column. If some rows are in hours and some in minutes, you do not have a column you can add up. Either convert to one unit in a new column, or split the column in two. Writing the unit into the heading, so it reads "Minutes" rather than "Duration", makes the next person's mistake much harder to make.

The export's own heading block sits above the data, so the header row is row 5 rather than row 1. This is a three-row excerpt of the six source records:

Cedar Ridge Community Center
After-school sessions export, September 2026

id       site    date        learners  duration  unit      fee charged
CR-101   Cedar   2026-09-01  18        45        minutes   $120.50
CR-102   Cedar   2026-09-02  12        1.5       hours     $89.00
CR-103   Ridge   2026-09-02  21        90        minutes   $240.00

Three problems emerge from the displayed rows and the Source cell values.

The duration column mixes 45 with 1.5. Adding it gives 136.5 of nothing at all.

The fee charged cells in this Source export are stored as text, with leading apostrophes in the workbook values. The displayed dollar signs alone do not prove a text type; numeric currency cells can also show them. SUM over these text fees returns 0, not because the fees are zero.

There is no as-of date anywhere. "September 2026" is a period, not the moment the file was produced, so nothing here can be compared against a later export with any confidence.

The fee column above holds $120.50, $89.00 and $240.00 as text. Before you read on, decide what each of these three formulas returns over those three cells.

=SUM(G6:G8)
=COUNT(G6:G8)
=COUNTA(G6:G8)

Here is the first attempt at converting the duration column, written for a row that happens to be in hours:

=E3*60

It gives the right answer for CR-102 and the wrong answer for every row already recorded in minutes, turning 45 minutes into 2,700. The formula is not wrong about arithmetic. It is wrong about which question it is answering: it assumes a unit instead of reading one.

Change it so the row's own unit decides:

=IF(F3="hours",E3*60,E3)

Now every row carries its own answer. Be clear about what this formula does not do: a row recorded next month in a third unit — seconds, say — falls through the IF and is treated as minutes, silently and plausibly. IF protects you from the units you thought of. The check that protects you from the ones you did not is a separate one, counting how many rows hold a unit you recognise, and it belongs next to the total.

A volunteer reports that the sessions sheet says the program ran for 184.5 minutes across six sessions, which would be about thirty-one minutes per session. Nobody believes it — three of those sessions were an hour and a half or more — but nobody can say where the figure went wrong either.

Work through it in this order, and change nothing until the last step.

1. Look at the column, not the total. Column E holds 45, 1.5, 90, 1, 45, 2. Three of those are recorded as hours; the numeric values alone do not tell you which.
2. Find the column that says so. Column F holds the unit for each row. The information needed to fix this was in the sheet the whole time; the total simply ignored it.
3. Decide the unit before writing anything. Minutes, because three of the six rows already are and a common small unit keeps these examples integral.
4. Put the answer in a new column. Do not overwrite column E. If the conversion turns out to be wrong, the original entry is the only way back.
5. Check one row by hand. CR-104 is 1 hour. Sixty minutes. If the formula does not say 60 for that row, stop and fix the formula rather than the row.

The habit worth taking from this: a total that surprises you is a signal about the columns underneath it, not a number to adjust.

The Sessions sheet is a working copy; the Source sheet is the export, left untouched. Fill in the minutes column, record the two assumptions, and complete the two checks.

Nothing you type here changes a file on your computer, and there is no spreadsheet application involved. Move with the arrow keys, press Enter to edit a cell and Escape to cancel, and use the formula bar above the grid if you prefer typing there.

Cedar Ridge also lends equipment, and that export has the same shape of problem in a different place:

item      site    borrowed    returned    quantity  size
BALL-01   Cedar   2026-09-02  2026-09-05  6         standard
BALL-02   Ridge   2026-09-02              12        junior
NET-04    Hill    2026-09-03  2026-09-03  1         full

On paper, answer three questions before doing anything in a grid.

1. Which date column has a missing value, and what should be done with that row?
2. The quantity column mixes items counted individually with items counted by the case. Where would you record which is which, and what would the corrected column be called?
3. A colleague suggests filling the blank returned date with the borrowed date "so the column is complete". Give one specific figure this would make wrong.

There is no grid for this one on purpose: the judgement happens before the typing.

Sign in to track your progress on this exercise.

You are ready to move on when you can do all four of these without looking anything up.

• Point at a worksheet and say which cells are raw data, which are inputs, which are calculations and which are outputs.
• Explain why a number stored as text does not break a total loudly, and name the two functions whose disagreement reveals it.
• Say what is wrong with a column headed "Duration" that holds both 45 and 1.5, and describe two different ways to fix it.
• Say why a blank cell and a zero are different answers, and give one situation where treating a blank as zero would change a decision.

Sign in to track your progress on this exercise.

A worksheet is checkable when its four regions are visibly separate, every column holds one type and one unit, and the assumptions and as-of date sit where a reader can find them without opening a formula.

Next: Write and copy formulas. The minutes column you just built needed the same shape of formula six times. There is a way to write it once — and a specific way it goes wrong that produces a column of plausible, incorrect numbers.