How to Create a Pair of Rolling Dice in Excel

Make a graphic dice roller in a worksheet

By

Updated on January 5, 2020

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 AND, IF, 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.

Open a blank worksheet in Excel and select cells D1 to J3.
Select Format in the Cells group of the Home tab.
Select Row Height and enter 24.75. Select Column Width and enter 5.
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.
Select Wingdings in the Font list and set the Font Size to 36.
Select cells D1 to F3.
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.
Select cells H1 to J3.
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.

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.

Select cell E5 under the Blue die.
Select the Formulas tab.
Select Math & Trig from the Function Library group.
Select RANDBETWEEN in the list to bring up the function dialog box.
Enter 1 in the Bottom field and 6 in the Top field.
Select OK. A random number between 1 and 6 will appear in cell E5.
Enter the same formula into cell I5. A random 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"," ")
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.

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: