Think of a number between one and ten. Congratulations. You just came up with your own random number. Now, let’s learn how to do the same exact thing in Excel.
Random function in Excel
To generate a random number in Excel, you will need to use the RAND function. The RAND function is one of the easiest functions to use in Excel. To use it, just navigate to any blank cell and enter the following:
Sample Result: 0.159959
NOTE: The result of the RAND function will update every time you make a change to your spreadsheet. If you want to keep your original random number, you will need to copy and paste the number to a new cell without the use of the RAND function.
When you enter this in your Excel spreadsheet, the result may not be what you expect. That’s because, the random function is Excel generates a long decimal number between zero and one. If this is what you need in your spreadsheet, you’re in luck. There’s nothing else you need to do other than use the RAND function.
On the other hand, if you were wanting to generate a random number between 0 and 100, you just need to combine a little math with the use of your RAND function. Something like this will work:
Sample Result: 67.34983
Check out our Excel Lesson Videos to learn how to use more common mathematical functions in Excel.
Restricting to whole numbers
If you want to restrict your random numbers to whole numbers only, you can either format the cell to contain zero decimals or you can use Excel’s TRUNC function to truncate the decimal portion of your random number. The latter would look something like this:
Sample Result: 73
What if you want to generate a random number with a low value other than zero? For example, let’s say you want to generate a random whole number between 10 and 20. To do this, you’ll need to use the following formula template:
=TRUNC(RAND() * (max-range – low-range) + low-range)
Using this template, the formula to generate a random number between 10 and 20 would look like this:
=TRUNC(RAND() * (20 – 10) + 10)
Sample Result: 12
While the above formula works well to generate a random integer using a specified range, Excel has another function called RANDBETWEEN that serves the purpose much better and is much easier to use. Here’s the same example of generating a random whole number between 10 and 20 using the RANDBETWEEN function:
Sample Result: 14
Both the RAND function and the RANDBETWEEN function in Excel have their purpose. Use the one that best fits your need when you want to generate a random number.