Excel Rolling Dice Tutorial

This tutorial covers how to create a dice roller program in Excel and uses formatting techniques to graphically display one face of a pair of dice.

The dice will display a random number generated by the RANDBETWEEN function. The dots on the die faces are created using the Wingdings font. A combination of ANDIF, and OR functions control when dots appear in each cell of the dice. Depending on the random numbers generated by the RANDBETWEEN functions, dots will appear in the appropriate cells of the dice in the worksheet. The dice can be "rolled" repeatedly by recalculating the worksheet

 

01
of 09

Excel Dice Roller Tutorial Steps

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

The steps to build an Excel Dice Roller are as follows:

  1. Building the Dice
  2. Adding the RANDBETWEEN Function
  3. The Functions behind the Dots: Nesting the AND and IF Functions
  4. The Functions behind the Dots: Using the IF Function Alone
  5. The Functions behind the Dots : Nesting the AND and IF Functions
  6. The Functions behind the Dots: Nesting the OR and IF Functions
  7. Rolling the Dice
  8. Hiding the RANDBETWEEN Functions

 

02
of 09

Building the Dice

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

The steps below cover the formatting techniques used to graphically display one face of a pair of dice in your worksheet to create the two dice.

The formatting techniques applied include changing cell size, cell alignment, and changing font type and size.

  • Drag select cells D1 to J3
  • Set the row height of these cells to 25
  • Set the column width of these cells to 5
  • Set the horizontal cell alignment to center
  • Set the vertical cell alignment to middle
  • Set the font type to Wingdings
  • Set the font size to 30

Dice color

  1. Drag select cells D1 to F3
  2. Set the cell background color to blue
  3. Drag select cells H1 to J3
  4. Set the cell background color to red
03
of 09

Adding the RANDBETWEEN Function

The RANDBETWEEN Function
The RANDBETWEEN Function. © Ted French

The RANDBETWEEN function is used to generate the random numbers shown on the two dice.

For the First Die

  1. Click on cell E5.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Math & Trig from the ribbon to open the function drop down list.
  4. Click on RANDBETWEEN in the list to bring up the function's dialog box.
  5. Click on the "Bottom" line in the dialog box.
  6. Type the number 1 (one) on this line.
  7. Click on the "Top" line in the dialog box.
  8. Type the number 6 (six) on this line.
  9. Click OK.
  10. A random number between 1 and 6 should appear in cell E5.

For the Second Die

  1. Click on cell I5.
  2. Repeat steps 2 to 9 above.
  3. A random number between 1 and 6 should appear in cell I5.
04
of 09

The Functions behind the Dots (#1)

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

In cells D1 and F3 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, it places an "l" in cells D1 and F3. If not, it leaves the cells blank ("").

To get the same result for the second die, in cells H1 and J3 type the function:

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

Remember: The letter "l" ( lowercase L ) is a dot in the Wingdings font.

05
of 09

The Functions behind the Dots (#2)

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

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 an "l" in cells D2 and F23. If not, it leaves the cell blank ("").

To get the same result for the second die, in cells H2 and J2 type the function:

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

Remember: The letter "l" ( lowercase L ) is a dot in the Wingdings font.

06
of 09

The Functions behind the Dots (#3)

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

In cells D3 and F1 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 an "l" in cells D1 and F3. If not, it leaves the cells blank ("").

To get the same result for the second die, in cells H3 and J1 type the function:

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

Remember: The letter "l" ( lowercase L ) is a dot in the Wingdings font.

07
of 09

The Functions behind the Dots (#4)

Excel Dice Roller Tutorial
Excel Dice Roller Tutorial. © Ted French

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 E2 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, in cell I2 type the function:

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

Remember: The letter "l" ( lowercase L ) is a dot in the Wingdings font.

08
of 09

Rolling the Dice

Rolling the Dice
Rolling the Dice. © Ted French

To "roll" the dice, press the F 9 key on the keyboard.

Doing this, causes Excel to recalculate all functions and formulas in the worksheet. This will cause the RANDBETWEEN functions in cells E5 and I5 to generate another random number between 1 and 6.

09
of 09

Hiding the RANDBETWEEN Function

Hiding the RANDBETWEEN Function
Hiding the RANDBETWEEN Function. © Ted French

Once the dice are complete and all functions have been tested to ensure they are operating correctly, the RANDBETWEEN functions in cells E5 and I5 can be hidden.

Hiding the functions is an optional step. Doing so adds to the "mystery" of how the dice roller works.

To Hide the RANDBETWEEN Functions

  1. Drag select cells E5 to I5.
     
  2. Change the font color of these cells to match the background color. In this case, change it to "white".