Learn How To Create Random Numbers With This Microsoft Excel Tutorial

Problem: You want to create a range of random numbers in order to illustrate a concept in a class. As shown in Fig. 293, you want to fill in random numbers in columns C through E.

Note: Click any image below for a larger view.

Strategy: Use the RAND function. This function will return a random number between 0 and 0.99999.

You will often need to create random numbers between a range of digits. In Fig. 294, you might want sales between 1 and 100 for each cell. Use a formula such as =RAND()*100 to return figures between 0 and 99.999. To return just integers, use the INT function to return the integer portion. =INT(RAND()*100) will return numbers such as these shown in Fig. 295.

Note that the formula is returning values from 0 to 99. If you really want values from 1 to 100, then use =INT(RAND()*100)+1. See Fig. 296.

Additional Information: Every time that you hit F9 or enter a new value in the worksheet, the random numbers will change. You might want to change the formulas to values to freeze the random numbers. Select the range of random numbers. Use Edit – Copy, and then use Edit – Paste Special – Values to convert formulas to numbers.

Summary: The RAND function can be used to return random numbers.

Commands Discussed: Edit – Paste Special – Values

Functions Discussed: =INT(); =RAND()

See all Microsoft Excel tips


Fig. 293
Fig. 294
Fig. 295
Fig. 296

References and Additional Resources

If you're looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different formatting techniques and even how to play games like Craps in Excel.

Other Resources:

Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/

Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.