Building a Dice Probability Table in a Spreadsheet

Start with every outcome

A dice spreadsheet works best when it lists the outcomes rather than relying on memory of a formula. For two six-sided dice there are 36 equally likely ordered pairs, which is the idea behind a sample space. Laying them out in a grid makes it easy to see why a total of 7 is more common than a total of 2.

Put the numbers 1 to 6 across the top of a grid and down the left side. Say the header row is B1:G1 and the left column is A2:A7. In cell B2, enter:

=$A2+B$1

The dollar signs fix the row or column so the formula can be copied across the whole 6 by 6 block B2:G7. Each cell now shows the sum of one die from the row and one from the column.

Count how often each total appears

Next, make a column of possible totals, 2 to 12, starting in I2. In J2, count how many grid cells equal that total:

=COUNTIF($B$2:$G$7,I2)

Fill it down to J12. You should get 1, 2, 3, 4, 5, 6, 5, 4, 3, 2, 1. A quick check: =SUM(J2:J12) must equal 36. If it does not, the grid or the range is wrong.

Convert counts to probabilities

In K2, divide each count by the total number of outcomes:

=J2/SUM($J$2:$J$12)

Format the column as a percentage. The values should be:

The probabilities in K2:K12 should add up to 100 percent. Rounded displays can look off by a hundredth, which is only a display effect.

Add cumulative columns

Many game questions ask for “at least” or “at most” a value, such as needing 8 or more to succeed. In L2, use a running total:

=SUM($K$2:K2)

This gives the chance of rolling that total or lower. For “that total or higher”, subtract from 1 and add back the row’s own probability: =1-L2+K2. Check it with a known value: the chance of rolling 8 or more on two dice is 15 of 36, or about 41.67 percent, so your sheet should show that.

Extend to other dice

The same layout adapts easily. To change the die size, change the header numbers, so 1 to 8 gives an eight-sided die. For three dice, a flat grid is awkward, so a common route is one column per die with all combinations listed. For three six-sided dice that is 216 rows, which is easy to fill with formulas or by pasting a generated list. If the question involves rolling several dice and keeping some, the combinations multiply quickly, and the shape of the problem is covered by dice pools in tabletop systems.

For a fair weighted die, such as one with faces that appear unevenly, replace the grid with a column of faces and a column of weights, then multiply weights together to get the weight of each combination.

Check the table with random rolls

A table built from formulas is exact for fair dice, but it is worth confirming that it matches reality. In a new sheet, enter in A2:

=RANDBETWEEN(1,6)+RANDBETWEEN(1,6)

Fill it down 1,000 rows, then count each total with COUNTIF and divide by 1,000. Totals near the middle will land around the table values but rarely hit them exactly. For a 7, the expected share is 16.67 percent, so about 167 out of 1,000, and a result a bit above or below that is normal. Recalculating the sheet gives a new set each time, a vivid reminder of how much short-run results move around.

If you want to test a physical die rather than a formula, record real rolls in a column and use the same counting method. Whether observed counts differ from the fair-die expectation by more than luck would explain is a separate question, and a chi-square test is the usual tool for it.

Common spreadsheet slips

• Forgetting dollar signs, so formulas drift when filled down.
• Using a range that is one row short, which makes counts total less than 36.
• Comparing a percentage to a count because one column was formatted unusually.
• Leaving volatile RANDBETWEEN formulas live while trying to study a result, since each edit changes them. Paste as values to freeze a run.

Once the table is right, it becomes a reusable reference. Looking up the chance of a target total is then a matter of reading a cell, and the same pattern, count and divide, applies to nearly any dice rule you can enumerate.