Track Fermentation Data in a Spreadsheet

Your sheet is two tables, not one

Start here; everything else follows. A fermentation record is not one table. It is two.

batches holds the facts that are true once: brew date, knockout time, volume, OG, yeast strain, generation, pitch rate, dissolved oxygen at knockout. One row per batch, forever. readings holds the facts that repeat: timestamp, gravity, sample temperature, tank temperature, pH. One row every time somebody walks out to the tank with a thief.

Hadley Wickham wrote the rule down in Tidy Data (2014): each variable is a column, each observation is a row, each type of observational unit gets its own table. The third rule is the one brewers skip. A batch and a reading are different kinds of thing — give them different tables.

Build one giant table instead and you get one of two outcomes, both familiar. Either you retype the OG on all fourteen rows of a batch, and the day one disagrees with the other thirteen nothing can tell you which is right. Or you merge the cell down four rows, which breaks sorting (only the first row of a merge sorts; the rest strand), blocks filters, and drops a (blank) category into every pivot built on it.

Two tables joined by batch_id. That is the trick. Two more tabs finish the file: lists for the allowed values behind every dropdown, specs for the per-style bands you check against.

One row per reading beats the wide sheet you actually built

Long format is one row per observation. Wide is one column per observation. Brewers build wide, because for the first three batches wide fits on one screen and reads like a brew sheet. Then it stops. Five wide-format failures — you'll know at least three:

• A new tab per batch. Sixty tabs, sixty near-identical layouts, no way to ask one question that spans two of them.
• Columns named Day 1 through Day 14. Then a lager runs sixteen days and recording a measurement needs a schema change.
• The batch name merged across four rows so it reads like a heading. It isn't a heading. It's data.
• Fill colour as status. Yellow means watch it, red means dumped, and that legend is written down nowhere.
• Two facts in one cell — 1.024 @ 66F — or the unit typed into the value, 9.4 ppm. Both turn the column to text, and text does not average.

The case isn't aesthetic. A wide sheet needs a schema change when a ferment runs a day longer than the last one. A long sheet needs a row. Every chart, pivot, import, dashboard and database downstream expects one row per observation. Building wide today is choosing to do the migration twice.

The minimum field set, and why each column earns its place

You don't need forty columns. You need the handful that forecast a good or bad batch. Gravity is the backbone; every downstream check leans on it. Tank temperature drives ester and fusel production, and a 4 °F drift shows up in the glass. pH is the fastest, cheapest health proxy you have — wort in around 5.0–5.2, ales finishing 4.0–4.5, lagers roughly 4.2–4.7, and anything below about 3.6–3.7 telling you about contamination rather than attenuation. Timestamp, not date: a 06:00 and an 18:00 reading are two different observations. batch_id and tank_id are the keys that let you join anything to anything.

Three columns need more of an argument, because these are the three people leave out.

Pitch rate goes on the batches tab in million cells/mL/°P — 0.75 for a standard ale, 1.5 for a standard lager — not "two bricks" or "the usual slurry". Store the rate you targeted and the viability you measured; the yeast pitch rate calculator → does the arithmetic.

The column spec you can build today

Four tabs: readings, batches, lists, specs. Here is the readings tab, column by column — build it in this order and you can log a batch this afternoon.

The batches tab carries what happens once: batch_id, brand, style, tank_id, brew_date, knockout_ts, volume_bbl, og_sg, ff_fg_sg, yeast_strain, yeast_generation, pitch_rate, pitch_viability_pct, do_ko_ppm, ko_temp_f, fg_sg (formula), adf_pct (formula), status. Store volume_bbl as a number — a barrel is 31 US gallons by federal definition (27 CFR 25.11) — and let the sheet convert.

The forced-ferment column nobody builds

That ff_fg_sg field is the one I'd add first. A forced fermentation — aseptic sample after the heat exchanger, heavily overpitched, warm, agitated, read at 36–48 hours — gives the true minimum FG for that wort. It answers the only question that matters during a slow ferment: brewhouse, or fermenter? If the tank won't reach the forced-ferment FG, it's pitch rate, temperature, oxygenation or yeast health. Not the mash.

Cider and mead: one more long table

Same two tables, plus one. Apple juice YAN commonly lands under the ~140 mg N/L generally taken as a clean-fermentation minimum; honey must runs single digits to low twenties ppm FAN against a yeast requirement of 150–350 ppm YAN, which is why staggered additions exist. Don't add five columns named Nutrient 1 through Nutrient 5. Add an additions tab — batch_id, timestamp, addition_type, amount, unit. Anything you add on a schedule is a repeating observation.

Units, dropdowns, and the rules that stop a fat-fingered decimal

This is the section to screenshot. It takes an afternoon once and saves you a year of quiet corruption.

The unit lives in the column name, never in the cell

gravity_sg, not "Gravity". temp_f, not "Temp". do_ppm, not "DO". The moment somebody types 9.4 ppm the column becomes text, AVERAGE returns 0, and nothing warns you. Watch the ppm/ppb trap too: wort DO at knockout is ppm, packaged total package oxygen is ppb against a benchmark under 50. Same analyte, 1,000× apart.

Store what you measured; compute everything else

Raw reading in one column, conversion in a formula beside it — never a converted number stored as though it were measured. A refractometer earns the rule on its own: factory-calibrated for sucrose, so wort needs a correction factor around 1.02–1.06 (1.04 being the common default), and once alcohol is present it reads high without a separate correction. That's why reading_method is a column. Otherwise you'll chase a stuck fermentation that finished four days ago.

Dropdowns, set to reject

Every categorical column comes from the lists tab: style, brand, tank_id, yeast_strain, reading_method, taken_by, status (fermenting / diacetyl rest / crashing / conditioning / packaged / dumped). Google Sheets sources a dropdown from a range so the list stays live, and offers two rejection modes. "Show a warning" accepts the bad value with a flag; only "Reject the input" enforces anything. Free text gives you FV3, FV-3, fv3, Fermenter 3 and #3 — five tanks where you have one.

Six numeric bounds that catch real mistakes

• gravity_sg — 0.990 to 1.180. Rejects 1.24 (the classic missing zero for 1.024) and a bare 24.
• temp_f — 28 to 110. Catches a Celsius value in a Fahrenheit column.
• ph — 2.8 to 6.0. Catches the pH reading typed into the gravity column.
• do_ppm — 0 to 25. Anything higher is a ppb value in the wrong home.
• yeast_generation — whole number, 0 to 30. Integers only, no "G4".
• batch_id — dropdown sourced from the batches tab. Referential integrity, in a spreadsheet.

Format the ID columns as plain text before any data goes in

A batch code like 26102-F3 becomes a date on paste under Excel's defaults. Leading zeros vanish. Numbers with colons become times. It's the same silent-corruption mechanism that turned the gene symbol SEPT1 into a date in roughly a fifth of surveyed genetics papers — no prompt, no flag. Pre-format the column as text and use ISO 8601 for dates. Julian lot codes shaped YYJJJ-T are a common industry convention and read fine, as long as the column is text before the first paste.

Attenuation and a per-style baseline, computed inside the sheet

Now the sheet stops being a log and becomes a QC view. Apparent attenuation is the number you'll live in:

ADF% = (OG − FG) / (OG − 1) × 100

Store OG and FG; compute ADF. A stored attenuation figure that disagrees with its own OG and FG is a bug you'll find eighteen months later. For °P from SG, the cubic the attenuation calculator → uses:

°P = −616.868 + 1111.14·SG − 630.272·SG² + 135.997·SG³

One trap: strain sheets quote apparent attenuation, typically 75–82% for an ale strain. Real degree of fermentation runs about 0.81× that. Quote the wrong one at a tank and you'll call a stall on a beer that finished where it should have. The ABV calculator → handles the alcohol maths off the same two numbers.

Five formula moves, and no more

1. Attenuation to date, any reading row: =(og − gravity_corrected)/(og − 1)*100.
2. Terminal gravity per batch: INDEX(range, MATCH(MAX(ts), ts, 0)), or a QUERY with order by reading_ts desc limit 1.
3. The baseline, from your own packaged history rather than a book: AVERAGEIFS(adf, style, X, status, "packaged") for the centre, STDEV over the same filter for the width.
4. The flag: conditional formatting on ABS(adf − baseline) > band. Band width is a judgement about your process, not an industry standard — set it from your own spread and tighten as history grows.
5. The trend: one SPARKLINE per batch row, so fourteen readings become a shape you read at a glance.

Then one pivot, the highest-yield object in the file: style × yeast generation, average ADF as the value. That's the answer to "how many generations can I actually run on this strain?" in a form you can point at. Excel's shapes differ — Tables, Power Query, dynamic arrays — but it's the same five moves.

What most people get wrong

1. Storing computed values instead of computing them. A typed attenuation figure drifts out of agreement with its own OG and FG. Store measurements; let formulas do the rest.
2. One free-text column becoming five. It starts as a convenience and ends as five spellings of one tank. Exactly one free-text field: notes.
3. Using date instead of timestamp. Two readings a day is normal, and a date column throws away which was which.
4. Colour as data. Fill colour doesn't sort, filter, pivot or survive an export. If it means something, it's a column.
5. Building wide because the first three batches fit on one screen. They always do. Batch sixty doesn't, and by then the migration is a weekend.

When the spreadsheet stops being enough (the honest ceiling)

I build the software that replaces these sheets, so take this in the spirit it's meant: for a nano brewery a well-shaped spreadsheet is the right answer for a long time. Here is where it runs out.

• Volume. Sheets caps at ten million cells, but you feel it long before that — performance degrades past roughly a hundred thousand rows, faster with heavy formulas and charts.
• No queryable audit trail. Version history exists, but you can't ask "who changed this pH, and when" and get an answer back as data.
• Nothing computes when the file is closed. The spec check runs when a tired human remembers to open it, which is precisely when it doesn't.
• No alerting. The sheet can't wake anyone at 9pm about a stalling tank. That isn't a setting; it's the tool.
• Concurrency. Two people editing the same row from a wet cellar floor is a coin flip.
• Referential integrity across files. Inside one file dropdowns hold. Across separate files it's a convention, and conventions decay.

Named plainly: a spreadsheet is a strong capture-and-check layer and a poor alert-and-learn layer. That's exactly the boundary in the capture → check → alert → learn loop from the brewery QC automation guide →. Get capture and check right in a sheet and the day you want alerting you'll migrate clean data instead of rebuilding four years from sixty tabs. There's a compliance edge to the same argument: 27 CFR 25.292 requires daily records of operations, and those are the source documents behind your BROP. A structured sheet isn't a compliance product; a messy one guarantees you'll reconstruct numbers from memory when somebody asks.