How to Create a Pair of Rolling Dice in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 25 25 people found this article helpful

How to Create a Pair of Rolling Dice in Excel

Make a graphic dice roller in a worksheet

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on January 5, 2020 Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section Build the Dice Add RANDBETWEEN Function The Functions Behind the Dots Roll the Dice Hide RANDBETWEEN Function 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: Select cells E5 to I5. 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? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Make a Schedule in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function How to Subtract Dates in Excel Use Excel's EOMONTH Function to Add or Subtract Months How to Combine the ROUND and SUM Functions in Excel How to Merge and Unmerge Cells in Excel How to Use the Google Spreadsheets AVERAGE Function Use the Excel RIGHT Function to Extract Characters How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Use the DAY function in Excel How to Use the MONTH Formula in Excel Ignore Error Values When Finding the Average in Excel Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Use the Round Function in Excel How to Create an Excel Lookup Formula With Multiple Criteria Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies