The throw of a dice is a popular away of determining an outcome in board games. This random chance of a number between 1 and 6 provides that element of luck to games.
This tutorial demonstrates how to create a simulation of a dice throw in Excel.
Download the completed dice throw simulation spreadsheet
Format the Dice to Show Spots
The first step is to format the cells so that they look like the side of a die.
- Select the range of cells you want to use for the dice. Cells D2:F4 have been used in this example
- Click the Border list arrow on the Home tab and select Outside Borders
- Click the Font list arrow and select Wingdings (a lowercase l in Wingdings is displayed as a spot)
Generate a Random Number Between 1 and 6
We need a function to generate a random number between 1 and 6 just like the roll of a dice. For this we need the RANDBETWEEN function.
- Select the cell you want to use to generate the random number simulated by the dice throw in Excel. Cell A1 is used in this example
- Enter the formula =RANDBETWEEN(1,6)
- This number can then be hidden by formatting the text to white
Display the Correct Number of Spots for the Number Generated
The next step is to make the dice display the correct number of spots to the number returned by the RANDBETWEEN function.
To do this we will use the IF function with the OR and AND functions to handle multiple criteria.
- Select the cells in the top left and bottom right of the dice (D2 and F4 in this example) and enter =IF(AND(A1>=2,A1<=6),”l”,””)
- Select the cells in the top right and bottom left (F2 and D4) and enter =IF(AND(A1>=4,A1<=6),”l”,””)
- Select the cells in the middle left and middle right (D3 and F3) and enter =IF(A1=6,”l”,””)
- Select the centre cell (E3) and enter =IF(OR(A1=1,A1=3,A1=5),”l”,””)
Click a Button to Roll the Dice
The RANDBETWEEN function will run every time you edit a cell or use a command in the spreadsheet. To limit it to the click of a button to roll the dice we need to turn off automatic calculation, and then insert a command button containing a macro to calculate the sheet.
- Click the Formulas tab on the Ribbon
- Click the Calculation Options button and select Manual
- Click the View tab on the Ribbon
- Click the list arrow on the Macros button and select Record Macro
- Enter a Macro name: and select This Workbook from the Store macro in: list
- Click Ok to begin the recording
- Click the Formulas tab and click the Calculate Sheet button
- Click the Stop Recording button on the Status Bar
- Click the Developer tab on the Ribbon
- Click the Insert button, select the Button (Form Control) and draw the button onto the sheet
- The Assign Macro dialogue box appears. Select the required macro and click Ok
The spreadsheet is complete. Each click of the button will initiate another dice throw in Excel.