Build Your Own NZ Lotto Results History Spreadsheet

A surprising number of Kiwis keep a Lotto spreadsheet. Some track their syndicate's lines, some are just curious about the numbers, and some — I see you — want to test whether the hot numbers are actually hot. All three are reasonable reasons. This guide shows you how to build a clean results-history sheet, where to get the data, which formulas are actually useful — and where the spreadsheet's power ends.

Step 1: decide what the sheet is for

This matters more than it sounds, because it decides your columns. In practice there are three common shapes:

1. A draw-history sheet — one row per draw, the numbers as drawn. This is the foundation, and what this guide mostly covers.
2. A personal tracker — your lines plus the draw results, so you can see every win and miss over time.
3. A frequency-analysis sheet — the history plus counts, to see how often each ball has appeared.

All three start from the same data, so build the history sheet first and the others grow out of it.

Step 2: get the data

Your sources, in order of trustworthiness:

• Official results — mylotto.co.nz publishes every draw's winning numbers and dividends. This is the authoritative source for any draw.
• Our results archive — the Lotto results history pages list every draw with its six main balls, bonus ball and Powerball, and the Powerball results archive is a crawlable draw-by-draw list going back to 2018 — 1,400-plus draws, updated automatically after every Wednesday and Saturday.
• Dividends and prize splits — if you also want what each division paid, the official results pages carry the full dividend table for every draw.

One honest note on data hygiene: when you backfill history, spot-check a handful of draws against the official site. Transcription errors are the quiet killer of spreadsheet analysis — one swapped digit and your frequency table has a phantom.

Step 3: the columns that matter

Keep one row per draw, oldest or newest — just be consistent:

Two formatting choices save future-you real pain: put each main number in its own column (not "1, 4, 6..." in one cell), and store dates as actual dates. The first makes frequency formulas trivial; the second lets you filter and slice by era — which matters right now, because the Powerball pool changed from 10 balls to 14 on 13 September 2026. Any PB frequency counting that mixes pre- and post-change draws is comparing two different games, so add a column marking the era if you plan to analyse Powerballs.

Step 4: the formulas that earn their keep

Once the data is in, three formulas cover most of what people actually want:

• Ball frequency: =COUNTIF(B$2:B$1473, 7) counts how many draws ball 7 appeared in, where column B is Main 1. To count a ball across all six main columns, sum six COUNTIFs — one per column. Fill down for balls 1 to 40 and you have the full frequency table, the same one our Number Explorer renders from the live database.
• Hot/cold bands: the explorer's hot and cold labels use mean ± one standard deviation of the per-ball counts: =AVERAGE() and =STDEV() over your frequency row, then compare. It's a descriptive lens, not a predictor — more on that below.
• Your line tracker: with your played lines on a second sheet, =COUNTIF() against each draw's numbers tells you how many of your six came up per draw. Stack it across the history and you can see every miss and every Division 7 bonus line your syndicate ever earned. We did exactly this exercise honestly against 30 real generated lines in our backtesting article — the results were instructively ordinary.

Step 5: the honest ceiling of a spreadsheet

Here's the part I'd underline, as the person who maintains a much bigger version of this spreadsheet: a results-history sheet can describe the past perfectly and predict nothing at all. Every draw is an independent event. The frequencies converge on uniformity, the hot band is one standard deviation of noise, and no formula over 1,400 draws extracts a signal that changes Saturday's odds — our whole algorithm honesty FAQ exists because of how often this gets hoped otherwise.

What the sheet genuinely gives you: a record of your own play, a transparent view of how often each ball has appeared, and the specific pleasure of checking your own numbers against your own data. That last one is the real reason most people keep one, and it's a good enough reason on its own.

Play within your means, enjoy the data for what it is — and if the spreadsheet starts feeling like a system rather than a curiosity, the Gambling Helpline (0800 654 655) is free and there 24/7.