Occasionally while working with your Excel sheet, you will find that you need to generate random numbers in Excel. Here we will walk through how to generate random numbers in Excel and some examples of practical application.
How to use Excel RAND() Function to Generate Random Numbers
The RAND() function is the most common random number generation method used in Excel. It allows the user to generate random numbers ranging from 0 to 1.
Step 1: Click into the cell where you would like the random number to be stored.
Step 2: Type in “=RAND()” and hit ENTER.
After hitting ENTER you should see that Excel will generate a random number in the cell.
The number of decimal places can be adjusted to your desired amount by using the Increase or Decrease Decimals buttons in the Numbers section of the menu bar.
You can create additional cells with random numbers by either cutting and pasting into the new cell or by selecting the cell to be copied and then hovering your cursor over the bottom right corner of the same cell.
When hovering you should see your curser turn into a plus sign. Then click and hold the left mouse button and drag the function down to the final cell that you want to populated with the formula.
How to use Excel RANDBETWEEN() Function to Generate Random Whole Numbers Between a Range
Next let’s try generating a random whole number within a user defined range. To do this we will use Excel’s RANDBETWEEN() function.
Step 1: Again start by clicking on a cell to select the cell for the formula.
Step 2: Type in “=RANDBETWEEN()” .
Step 3: Select the range of desired random numbers by entering in whole numbers for the Bottom and Top values. Remember to make sure the numbers are separated by a “,“.
Step 4: Finally, close off the formula with a “)“. Hit Enter to generate the random number.
Again, you can copy the function to addition cells as needed by either cutting and pasting into a new cell or by dragging from the bottom right corner of the cell to the additional cells.
How to Generate a Random Decimal Number Between a Range
What if you need a random decimal number? Next, we will take a look at how the RAND() and RANDBETWEEN() functions can be used together to accomplish to generate a random decimal number. Let’s assume that we need the value to be within the same range as before.
Step 1: Select the desired cell again.
Step 2: Type in “=RAND()*RANDBETWEEN(1,100)“.
Step 3: Hit Enter to generate the number.
Step 4: (Optional) You can additionally and a Round() function to control the number of decimals of the random number.
For this example lets assume that we only want 2 decimals. For more detail on how to use the different Rounding functions, please check out our post on Excel Rounding.
And finally, click the bottom right corner and drag to the additional cells as desired.
How to Regenerate the Random Numbers
Now that you have entered in all of the functions for your random numbers, what happens if/when you need to regenerate the numbers?
Simply go to the “Formulas” tab and click on the “Calculate Now” button.
Now your random numbers will recalculate as will the other formulas and functions in the sheet.
Thanks for reading! We’d love to hear from you—what Excel challenges are you facing? Drop a comment or let us know what tutorials you’d like to see next! Finally, be sure to check out the Excel courses to help master your Excel skills.