Excel Rolling Dice Tutorial

01
of 09

Overview Excel Dice Roller Tutorial

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

Tutorial Overview

  • This tutorial covers how to create a dice roller program in Excel
  • The tutorial uses formatting techniques to graphically display one face of a pair of dice as seen in the image above
  • 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 AND , IF , 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

The steps in this tutorial are:

  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

Building the Dice

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.

  1. Drag select cells D1 to J3

  2. Set the row height of these cells to 25

  3. Set the column width of these cells to 5

  4. Set the horizontal cell alignment to center

  5. Set the vertical cell alignment to middle

  6. Set the font type to Wingdings

  7. 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

Return to Overview Page

03
of 09

Adding the RANDBETWEEN Function

The RANDBETWEEN Function
The RANDBETWEEN Function. © Ted French

Adding the RANDBETWEEN Function

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.

Return to Overview Page

04
of 09

The Functions behind the Dots (#1)

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

The Functions behind the Dots (#1)

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.

Return to Overview Page

05
of 09

The Functions behind the Dots (#2)

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

The Functions behind the Dots (#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 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.

Return to Overview Page

06
of 09

The Functions behind the Dots (#3)

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

The Functions behind the Dots (#3)

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.

Return to Overview Page

07
of 09

The Functions behind the Dots (#4)

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

The Functions behind the Dots (#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 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.

Return to Overview Page

08
of 09

Rolling the Dice

Rolling the Dice
Rolling the Dice. © Ted French

Rolling the Dice

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.

Return to Overview Page

09
of 09

Hiding the RANDBETWEEN Function

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

Hiding the RANDBETWEEN Functions

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".

Return to Overview Page