How to Create a Pair of Rolling Dice in Excel

Make a graphic dice roller in a worksheet

Using a few functions, you can create a dice roller that graphically displays a pair of dice on your worksheet.

Each die 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 for Microsoft 365, Excel 2019, 2016, 2013, and 2010.

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.

  1. Open a blank worksheet in Excel and select cells D1 to J3.

    Screenshot of D1:J3 selected in Excel
  2. Select Format in the Cells group of the Home tab.

    Screenshot of Format in Excel
  3. Select Row Height and enter 24.75. Select Column Width and enter 5.

    Screenshot of Row Height box
  4. Select Format Cells at the bottom of the Format drop-down menu and go the Alignment tab. Set the Horizontal Cell Alignment and the Vertical Cell Alignment to center and select OK.

    Screenshot of Format Cells dialog box
  5. Select Wingdings in the Font list and set the Font Size to 36.

    Screenshot of fonts in Excel
  6. Select cells D1 to F3.

    Screenshot of D1:F3 selected
  7. Right-click the selected cells and choose Format Cells. Go to the Fill tab and choose Blue for the Background Color. Select OK to apply the color and close the dialog box.

    Screenshot of blue selected as cell background color
  8. Select cells H1 to J3.

    Screenshot of H1:J3 selected in Excel
  9. Right-click the selected cells and choose Format Cells. Go to the Fill tab and choose Red for the Background Color. Select OK to apply the color and close the dialog box.

    Screenshot of Red selected as cell background color

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.

  1. Select cell E5 under the Blue die.

    Screenshot of cell E5 selected
  2. Select the Formulas tab.

    Screenshot of Formulas tab
  3. Select Math & Trig from the Function Library group.

    Screenshot of Math & Trig menu
  4. Select RANDBETWEEN in the list to bring up the function dialog box.

    Screenshot of function dialog box
  5. Enter 1 in the Bottom field and 6 in the Top field.

    Screenshot of RANDBETWEEN arguments
  6. Select OK. A random number between 1 and 6 will appear in cell E5.

    Screenshot of random number in E5
  7. Enter the same formula into cell I5. A random number between 1 and 6 will appear in cell I5.

    Screenshot of formula in 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.

  1. 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"," ")

    Excel's formula for creating rolling dice.
  2. 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", " ")

    Excel's formula for creating rolling dice.
  3. 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", " ")

    Excel's formula for creating rolling dice.
  4. 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.

Excel's formula for rolling dice.

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.

Was this page helpful?