How to Create a Pair of Rolling Dice in Excel

A pair of dice in mid-roll.

Bogdan Dreava/EyeEm/Getty Images

Did you know you could create rolling dice in Excel? Using a few functions, you can create a dice roller that graphically displays a pair of dice on your worksheet.

Each dice displays a random number generated by the RANDBETWEEN function. The dots on the die faces use the Wingdings font, and a combination of the ANDIF, and OR functions control when dots appear in each cell.

Depending on the random numbers the RANDBETWEEN function generates, dots will appear in the appropriate cells in the worksheet. The dice can be re-rolled repeatedly by recalculating the worksheet.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Building the Dice

First, you need to apply a few formatting techniques to display the dice on an Excel worksheet. These include changing the cell size and cell alignment, as well as font type and size.

A screenshot of Excel's Column Width formatting dialog box.
  1. Open a blank worksheet in Excel and select cells D1 to J3.

  2. Select the Format button in the Home tab.

  3. Set the Row Height to 24.75 and the Column Width to 5.

  4. Select Format Cells from the Format drop-down menu and go the Alignment tab. Set the Horizontal Cell Alignment and the Vertical Cell Alignment to center.

  5. Go to the Font tab. Set the Font Type to Wingdings and the Font Size to 36. Click OK.

  6. Select cells D1 to F3.

  7. Set the Cell Background Color to blue.

  8. Select cells H1 to J3.

  9. Set the Cell Background Color to red.

Add the RANDBETWEEN Function

Once you've finished formatting the cells, you need to input the RANDBETWEEN function in two cells to generate the random numbers shown on the dice as dots.

A screenshot of Excel's RANDBETWEEN function dialog box.
  1. Click on cell E5 under the Blue dice.

  2. Click the Formulas tab of the ribbon menu.

  3. Choose Math & Trig from the ribbon.

  4. Click on RANDBETWEEN in the list to bring up the Function Dialog Box.

  5. Click the Bottom line.

  6. Type the number 1.

  7. Click the Top line.

  8. Type the number 6.

  9. Click OK. A random number between 1 and 6 will appear in cell E5.

  10. Click on cell I5 under the red dice.

  11. Repeat steps 2 to 10 above.

  12. A number between 1 and 6 will appear in cell I5.

The Functions Behind the Dots

To generate the correct number of dots, you need to use the IF function in cells D1 to D3, E2, and F1 to F3 in the first dice, and in cells H1 to H3, I2, and J1 to J3 in the second. Some of the cells also include the AND or OR function.

Type or paste the formulas below into the formula bar, not directly in the cell, as this will mess up the cell's formatting.

In cells D1 and F1, type the following function:

=IF(AND(E5>=2, E5<=6),"l","")

This function tests to see if the random number in cell E5 is between 2 and 6; if so, the function places a lowercase L in cells D1 and F1, which is a dot in the Wingdings font. If not, it leaves the cells blank. To get the same result for the second die, type the following function into cells H1 and J1:

=IF(AND(I5>=2, I5<=6),"l"," ")
Setting up the dice display in Excel for Android

In cells D2 and F2, type the following function:

=IF(E5=6, "l", " ")

This function tests to see if the random number in cell E5 is equal to 6; if so, it places a dot in cells D2 and F2. If not, it leaves the cell blank. To get the same result for the second die, type the following function into cells H2 and J2:

=IF(I5=6,"l", " ")

In cells D3 and F3, type the following function:

=IF(AND(E5>=4, E5<=6),"l" , " ")

This function tests to see if the random number in cell E5 is between 4 and 6; if so, it places a dot in cells D3 and F3. If not, it leaves the cells blank. To get the same result for the second die, type the following function into cells H3 and J3:

=IF(AND(I5>=4, I5<=6),"l", " ")

In cell E2, type the following function:

=IF(OR(E5=1, E5=3, E5=5), "l", " ")

This function tests to see if the random number in cell E5 is equal to 1, 3, or 5; if so, it places an "l" in cell E2. If not, it leaves the cell blank. To get the same result for the second die, type the following function into cells I2:

=IF(OR(I5=1, I5=3, I5=5), "l", " ")

Rolling the Dice

To roll the dice, recalculate your worksheet. You can select Calculate Now under the Formulas ribbon (the icon that looks like a calculator), or you can press the F9 key in you're using a desktop version of Excel.

Select calculate now to

Recalculating causes the RANDBETWEEN functions in cells E5 and I5 to generate another random number between 1 and 6.

Hiding the RANDBETWEEN Function

Once the dice are complete and all functions have been tested to ensure they are operating correctly, you can hide the RANDBETWEEN functions in cells E5 and I5:

  1. Select cells E5 to I5.

  2. On the Home tab, change the font color of these cells to match the background color, which, in this case, is white.