Create a Random Number Generator in Excel
As the title of the article suggests, this article is about creating a button that will generate a random number. Each time the button is pressed, a new random number will appear.
RAND Function to Generate Random Numbers
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 the macro 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 the macro, 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 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
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