Create a Button in Excel to Generate Random Numbers - TurboFuture - Technology
Updated date:

Create a Button in Excel to Generate Random Numbers

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

Random numbers created in Excel using the RAND function.

Random numbers created in Excel using the RAND function.

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.

=RAND()

After inputting this formula into a cell, a randomized number will appear between 0 and 1.

Shows the RAND function entered into a cell in an Excel Spreadsheet. Alone, the RAND function can only provide a random number between 0 and .1 excluding .1. Multiplying this function by multiples of 10 can increase the range of possible numbers.

Shows the RAND function entered into a cell in an Excel Spreadsheet. Alone, the RAND function can only provide a random number between 0 and .1 excluding .1. Multiplying this function by multiples of 10 can increase the range of possible numbers.

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.

This illustration shows the RAND function with 4 different ranges. The calculations used to make 3 of those ranges possible are shown to the left of the results. Note that in this example, the only random number is in F4.

This illustration shows the RAND function with 4 different ranges. The calculations used to make 3 of those ranges possible are shown to the left of the results. Note that in this example, the only random number is in F4.

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.

These are the first three steps necessary to inserting a form control button.

These are the first three steps necessary to inserting a form control button.

Excel is now waiting for you to trace a button with your cursor. This will allow you to position and size your button.

This illustration shows the tracing of a form control button. This must be done right after click the form control button.

This illustration shows the tracing of a form control button. This must be done right after click the form control button.

Assigning a Recorded Macro to the Button

Next, a window will appear. Click the record button to start a recording process.

This window will appear immediately after the button is traced.

This window will appear immediately after the button is traced.

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.

This additional window appears to allow the developer to add a description of the macro process and add a keyboard short-cut to run the process.

This additional window appears to allow the developer to add a description of the macro process and add a keyboard short-cut to run the process.

To stop recording, click on the small square stop button in the lower left-hand corner of the screen.

To stop recording the macro be sure to click this square in the bottom left-hand corner of your screen.

To stop recording the macro be sure to click this square in the bottom left-hand corner of your 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


create-a-button-in-excel-to-generate-random-numbers

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