Another Spreadsheet Project

This is another fun project that will help you become more familiar with Excell functions.>
 
This was originally written for Appleworks on a MAC, but it also worked in Excel 97.  I have made a few changes to get it to work in Excel XP.  Some formulae will have to be modified.

Follow the directions below to create a pair of dice that actually work by holding down the Ctrl +  the = sign, (On Mac: Command + Shift), or F9 on Excel XP.
 

When you open a new Excel project you will get a blank spreadsheet hundreds of columns wide, and thousands of rows high. For now, all we need is seven columns wide and five rows high, so to make things visibly simpler we can hide all of the unused cells. This procedure will come in handy when creating other professional-looking spreadsheets and business forms.

To hide the extra columns:
Select the button at the top of the J column, (shown to the right - Select here). With the button selected hold down the SHIFT KEY. While the SHIFT KEY is held down click once on the END KEY and once on the RIGHT ARROW KEY in that order. You should notice that all of the cells to the right of the I column are highlighted.

Right-click anywhere in the highlighted cells and select hide in the window that appears. You should now be able to see only columns A to I, but you will still see thousands of rows that you don't need at this time.

To get rid of these extra rows select the button on number 6 row where it shows Select here. This time hold down the CTRL KEY and SHIFT KEY at the same time. While holding down these keys click once on the DOWN ARROW. All of the cells from rows 6 downward should now appear highlighted.

Right-click anywhere in the highlighted cells to bring up the pop-up window. In the window that pops up select "Hide" to hide all the selected rows.
Now we have a spreadsheet that is nine columns wide and five columns high. This is going to become the visual 'interface' for our pair of dice, with spaces for the score and comments.

Next step is to re-size the cells to represent the dice.
Click and drag the blue column buttons to select columns B to H. Right-click in the selected area to bring up the window and choose Column Width. Columns B, C, and D will be form die and FGH will form another. Columns A, E, and I will provide a border. Choose a width that will provide a suitable size for your computer screen.
Select rows 2, 3, and 4 by clicking and dragging downward. With the rows selected, right-click and choose Row Height. You might have to experiment to find the proper height.  You want to create a pair of dice that are square in shape.  The borders and space between the dice can be adjusted for appearance.
You can re-size columns and rows by moving the cursor between any two columns or rows.  When the cursor changes to the two-sided arrow click and drag the column or row to the desired width.

  1. In Cell B5 type the following formula into the Formula Bar:  =INT(RAND()*6)+1 . Make sure to type the equals sign. This will generate the random numbers of the dice from 1 to 6.

  2. In Cell B2 type the following formula into the Formula Bar =IF(B5>1,"l","") letter L, not 1.

  3. Cell D2 type =IF(B5>3,"l","")

  4. Cell B3 type =IF(B5=6,"l","")

  5. Cell C3 type =IF(OR(B5=1,B5=3,B5=5),"l","")

  6. Cell D3 type =IF(B5=6,"l","")

  7. Cell B4 type =IF(B5>3,"l","")

  8. Cell D4 type =IF(B5>1,"l","")

Select the range of 4x3 cells B2 to D5.  Right-click and select Copy from the pop-up window. Now select cell F2. Right-click and select Paste from the pop-up window.

Now you have two separate die.  Each one has a random number generator that will function independently of the either.

In Cell E4 type =B5+F5.  This will display the total of the pair of dice.

Now select the range of cells from B2 to D4 and change the font to Wingdings. To do this right-click the selected cells and choose Format Cells...
The Format Cells window will appear, allowing you to select from any of the install fonts.  Choose Wingdings. The letter "l" in Wingdings is a large dot. Change the Font Size to 36. You can change the color of the dots as well.  You might want to change to a Font Color that complements the background color of the cells, which we will change next.

Select cells F2 to H4 and repeat the process. You should now have a pair of "Dice".
Make sure you have the Home tab selected. Set both the horizontal and the vertical Text Alignment with the justification/alignment buttons. The dice will look more realistic with the dots centered.
Select the range of cells from B2 to D4 again and right-click. In the Format Cells window select Fill. Select a color for the first die. Do the same for the second die.

You can experiment with the colours of the dice, and the font colour and size. Make sure you choose Center Alignments so the dots will be centered in the squares.

I added another feature. If you type the following in any of the blank cells, such as E3, it will display the caption "Lucky Seven" whenever the pair of dice adds up to seven.

=IF((E4=7),"Lucky Seven","")

Make sure that the font for this cell is not set to Wingdings or you will get some unexpected results.

Right-click on the cell and choose Format Cells from the pop-up window.  This time choose the Alignment tab. Set the vertical and horizontal alignment for Center and select Wrap text. Wrap text will allow you to fit more text into a single cell.
Under the View tab  uncheck the checkmarks for Formula Bar, Gridlines, and Headings. Select all cells and Lock them (under Options) to prevent accidentally changing them.
To hide the Random numbers in Cell B5 and F5 change the font colour to match the cell colour.

I set the font color to White in cells E3 and E4, and the Fill color to black in the border cells.

Here it is - the finished product!

Roll the dice by holding down the Ctrl +  the = sign, (On Mac: Command + Shift), or F9 on Excel XP and Excel 2007.
 

Home