Create a Dice Roller in Google Sheets Using One Formula

While creating simple games in Google Sheets like Snakes and Ladders or Ludo, I wanted a dice roller that looked like a real dice and worked with just one formula. If you’d like the same idea, this tutorial will walk you through it step by step.

In this tutorial, you will learn how to create a dice roller in Google Sheets using one formula. This method uses a single ARRAYFORMULA to generate a visual 3×3 dice face that changes dynamically whenever you roll.

Why Use a Dice Roller in Google Sheets for Games and Templates?

A built-in Google Sheets dice roller can be useful for:

Board game templates
Classroom activities
Probability practice
Random number generation
Custom game dashboards

The best part is that you do not need any add-ons.

Step 1: Create a 3×3 Dice Grid

To visually match a real dice, you need a 3×3 square grid.

We will create the dice in the range:

C3:E5

The 3×3 layout gives it a more realistic look.

Resize the Columns

Select columns C to E by clicking the C column header and dragging across to E.
Right-click the selected columns.
Choose Resize columns C – E.
Set the width to 50 px.

Resize the Rows

Select rows 3 to 5 by clicking row 3 and dragging down to 5.
Right-click the selected rows.
Choose Resize rows 3 – 5.
Set the height to 50 px.

This creates a clean square area for the dice.

Step 2: Style the Dice

Now let’s make the dice roller in Google Sheets look better.

Apply the Following Formatting

Select C3:E5 and use the relevant tools in the Google Sheets toolbar to apply the following formatting.

Setting	Value
Fill Color	`#D62828`
Text Color	White
Font Size	24
Border Color (apply thick border)	`#8B0000`
Horizontal Align	Center
Vertical Align	Middle

I used a red-and-white theme because it resembles a classic board-game dice, but you can use any colors you like.

Remove Gridlines

If you are building a game board, hiding gridlines makes a big difference.

Go to:

View > Show > Gridlines

Uncheck it for a cleaner game-style appearance.

Step 3: Insert the Dice Roller Formula

Enter the following formula in cell C3:

=ARRAYFORMULA(
  LET(
    diceValue, IF(G4, RANDBETWEEN(1, 6), 0),
    spacerRow, SEQUENCE(1, 9)^0,
    dicePatterns, 
    {
      0,0,0,0,1,0,0,0,0;
      1,0,0,0,0,0,0,0,1;
      1,0,0,0,1,0,0,0,1;
      1,0,1,0,0,0,1,0,1;
      1,0,1,0,1,0,1,0,1;
      1,0,1,1,0,1,1,0,1
    },
    selectedPattern, XLOOKUP(diceValue, SEQUENCE(6), dicePatterns, 0),
    diceGrid, WRAPROWS(selectedPattern * spacerRow, 3),
    IF(diceGrid=0, , "●")
  )
)

The formula looks large at first glance, but most of it is just defining the pip positions for each dice face.

Step 4: Add a Tick Box to Roll the Dice

Go to cell G4.

Then click:

Insert > Tick box

Now click the tick box to roll the dice.

This creates a working Google Sheets dice roller.

How to Use the Dice Roller

Tick the tick box to roll the dice
Untick the tick box to clear the dice
Tick again for the next roll

Important Note About Recalculation

Because the formula uses RANDBETWEEN, the result may change whenever the sheet recalculates or another edit is made.

If you want to freeze the dice value until the next click, see the optional Apps Script method later in this guide.

How the Dice Roller Formula Works

Let’s understand how this dice roller in Google Sheets works.

1. Generate a Random Number

RANDBETWEEN(1,6)

This returns a number from 1 to 6.

2. Dice Face Patterns

This 6×9 array stores the pip positions for each dice face:

{
0,0,0,0,1,0,0,0,0;
1,0,0,0,0,0,0,0,1;
1,0,0,0,1,0,0,0,1;
1,0,1,0,0,0,1,0,1;
1,0,1,0,1,0,1,0,1;
1,0,1,1,0,1,1,0,1
}

Each row represents one dice number.

3. Select the Correct Dice Face

XLOOKUP(...)

This fetches the correct pip pattern based on the random number.

4. Convert to 3×3 Layout

WRAPROWS(...,3)

This converts the 9 values into a visual 3×3 dice grid.

5. Replace 1 With Dice Pips

IF(diceGrid=0,"","●")

1 becomes ●
0 becomes blank

That is how the visual dice is created.

Optional Tip: Freeze the Dice Roll with Apps Script

If you want the dice result to remain fixed until the next click, use this optional script.

What It Does

Tick tick box in G4
Rolls the dice
Stores the number in G3
Unticks tick box automatically
Keeps the value fixed until next click

Add the Script

Go to:

Extensions > Apps Script

Replace the existing code with:

function onEdit(e) {
  if (!e) return;  const sheet = e.source.getActiveSheet();  if (e.range.getA1Notation() === "G4" && e.value === "TRUE") {
    const roll = Math.floor(Math.random() * 6) + 1;    sheet.getRange("G3").setValue(roll);
    sheet.getRange("G4").setValue(false);
  }
}

Save the project.

Update the Formula

Replace this part:

IF(G4, RANDBETWEEN(1,6),0)

With:

G3

Now the dice result stays fixed until the next roll.

Final Thoughts

This is one of the easiest ways to create a dice roller in Google Sheets using one formula. It is perfect for custom games, classroom activities, and interactive spreadsheets.

If you want better control, the optional Apps Script method can freeze the dice value between rolls.

Now you have a fully working Google Sheets dice roller built with formulas. Once you build it, you can reuse the same dice roller in many future Google Sheets games.