How to Build a Sports Betting Model in Excel

Lay out the workbook

Keep raw data, calculations, and results on separate sheets so that a change in one place can’t silently overwrite another:

• Games: one row per team per game, with the date, the team, the opponent, home or away, points for and against, and the scoring margin.
• Predictions: one row per game you might bet, with each team’s rating going into the game, your projected margin, the market line and price, and the probability you derive.
• Results: the grading, profit or loss, and scoring columns.

Paste raw data as values and never edit it by hand. Formulas live on the other sheets and look rows up by date and team rather than by position.

Build ratings without peeking ahead

A simple starting model rates each team by its average scoring margin and projects a game as the home team’s rating minus the away team’s, plus a home-field term estimated from your own data. The trap is leakage: an average that includes the game you are predicting makes a backtest look far better than reality.

An AVERAGEIFS with a date condition keeps each rating honest. Say the Games sheet has dates in column A, teams in column B, and margins in column G, and each Predictions row has the game date in A2, the home team in B2, and the away team in C2. The home team’s rating going into the game is:

=AVERAGEIFS(Games!G:G, Games!B:B, B2, Games!A:A, "<"&A2)

That averages only games played before A2. Swap B2 for C2 to rate the away team, put the two ratings in D2 and E2, and set the projected home margin in F2 to D2 − E2 plus the home-field term. Early-season ratings rest on very few games, so consider blending in last season’s rating until a team has played several times.

Turn the projection into a probability

Spread betting needs a probability, not just a margin. A normal approximation is a reasonable first step. With the projected home margin in F2, the home spread in G2 (entered as −3.5 for a 3.5-point favorite), and a standard deviation of about 13.5 points for NFL margins, the chance the home team covers is:

=1-NORM.DIST(-G2, F2, 13.5, TRUE)

A projection of home by 4.0 against −3.5 gives about 51.5%. Next, convert the American price in H2 to implied probability and decimal odds:

• Implied probability: =IF(H2<0, H2/(H2-100), 100/(H2+100))
• Decimal odds: =IF(H2<0, 1-100/H2, 1+H2/100)

Expected value per $1 staked is then =I2*J2-1, where I2 holds your probability and J2 the decimal odds. At 51.5% and −110, that is about −1.7%, so the model’s lean is not a bet.

Test on a season you didn’t touch

Pick a cutoff date before you start. Build and adjust the model on games before it, then run the later games through once, without changes. Score the test period two ways:

• Brier score: =(I2-K2)^2 for each game, where K2 is 1 if the side won and 0 if it lost, averaged across the test period. Calculate the same score using the no-vig market probability; a model that can’t beat the market’s score has nothing to add.
• Calibration: use COUNTIFS and AVERAGEIFS to group predictions into ranges such as 50–55% and 55–60%, then compare each range’s average prediction with how often those sides actually won.

Leave pushes out of the probability scores and record them separately in the profit columns.

Habits that prevent silent errors

• Use Excel tables or full-column references so new rows are included automatically.
• Never sort a sheet in place after other formulas refer to it by position.
• Store dates as real dates, not text, so date conditions work.
• Keep a change log with every adjustment to the model and the date it was made.

A spreadsheet makes every step visible, which is its main advantage. It can’t make the data clean or the probabilities right; the out-of-sample test is what tells you that. The betting model guide covers evaluation in more depth.