Using the Random Function in Excel

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.

Using the Random Function 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:

=RAND()

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:

=RAND()*100

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:

=TRUNC(RAND()*100)

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

RANDBETWEEN function

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:

=RANDBETWEEN(10,20)

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.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!