Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
As the title of the article suggests, this text is about creating a button that will generate a random number. Each time the button is pressed a new random number will appear.
Using the RAND Function
The RAND function is very easy to use. This function returns a random number between 0 and 1 as a decimal (excluding 1). See the formula for the RAND function below in the illustration. This is the format used to input the function manually into a cell.
After inputting this formula into a cell, a randomized number will appear between 0 and 1.
Expanding Your Range of Randomized Numbers
To increase the range of numbers for the RAND function, you must multiply the function by multiples of 10. The illustration below shows the results after the RAND function is multiplied by 10, 100 and 1000. With each randomized range 10, 100 and 100 will be excluded from the results.
The results that are multiplied by multiples of 10 can be rounded up to a random whole number by changing the decimal position.
Inserting a Form Control Button
The following steps in the illustration below need to be taken to insert a button. This button will refresh the RAND function so that it can display a new result on every button click. If you do not have the developer tab enabled in Excel, learn how to do so here.
First, click on the developer tab and select insert. Next, click on the form control button option.
Excel is now waiting for you to trace a button with your cursor. This will allow you to position and size your button.
Assigning a Recorded Macro to the Button
Next, a window will appear. Click the record button to start a recording process.
Almost ready to record. After clicking through the next OK button, the process of refreshing the excel document will need to be recorded. After this is recorded, it will be assigned to the button and every time the button is pressed the excel workbook will refresh.
Select the Ok button to start the recording process. Now the only task before stopping the recording is to press F9. If your F keys don't work without an fn key you must hold the fn key before selecting F9.
To stop recording, click on the small square stop button in the lower left-hand corner of the screen.
The random number in F4 should change. This will change the other cells since they use F4 in their calculation. Instead of randomizing one cell and and using it as a reference you may multiply the RAND function by 10 to come up with larger ranges shown here:
=RAND()*10 Results in a randomized number from 0 - 10
=RAND()*100 Results in a randomized number from 0 - 100
=RAND()*1000 Results in a randomized number from 0 - 1000
To learn more about developing in Excel, I recommend taking a look at the book Microsoft Excel 2019 VBA and Macros (Business Skills) by Bill Jelen to enhance your skills.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2020 Joshua Crowder