How to Generate Random Numbers in Excel
If you need to generate one or more random numbers for business, education or other purposes, you can do it directly in Microsoft Excel. You can get random numbers using a random number generation tool or an Excel function.
Use the random number generator in Excel
With a Microsoft add-on called Analysis ToolPak, you can perform many statistical and engineering tasks such as finding a moving average. Another feature of the tool is a random number generator.
Add analytics tools
To see if you already have the add-on, go to the Data tab and the Analytics section of the ribbon. Look for the Data Analysis button. If you have the button, you can switch to using the tool.
If you don’t see the button, you can easily add it. Go to File> Options and select “Add-ons” on the left. At the bottom of the window, go to Manage and select “Excel Add-ins”. Click “Go”.
When the add-ons window opens, check the box next to Analysis Tools and click “OK”.
Use the random number generator
Go to the Data tab and click “Data Analysis” in the Analysis section of the ribbon. When the window pops up, select “Random Number Generation” and click “OK”.
Starting at the top of the window, enter the number of columns you want to fill using the Number of variables box. Then, enter the number of lines in the Number of random numbers box.
This generator is advanced in that you can select the distribution you want to use from options like Bernoulli, Binomial, Patterned and Discrete. Once you have selected your distribution from the drop-down list, the Parameters section will update with the information you need to complete.
In the Random Seed field, you can enter a starting number (up to 9999) for the generator to use if you wish. Then, choose one of the Output Options for the position where you want to display the random numbers.
Click “OK” when ready and you will receive your numbers.
Use the random number functions in Excel
Another option to generate random numbers in Excel is to use a function. There are three functions you can use. Whenever you recalculate or reopen the workbook, a new random number will be generated with these functions. They don’t offer as many options as the Random Number Generator tool, but they are easier to use.
The RAND function
With the RAND
function you can generate a random number greater than or equal to zero and less than one. This provides options for decimal numbers. But you can also get numbers greater than one by changing the formula.
For a basic random number, enter the following and press Enter:
=CASUALE()
For a random number greater than or equal to zero and less than 500, enter the following and press Enter:
=CASUALE()*500
For a random integer greater than or equal to zero and less than 500, type the following and press Enter:
=INT(CASUALE()*500)
The RANDBETWEEN function
Maybe you want to generate a number between two specific numbers. In this case you would use the function RANDBETWEEN
.
For a random number between 10 and 100, enter the following and press Enter:
=CASUALE TRA(10,100)
For a random number between 10 and 10 negatives, enter the following and press Enter:
=CASUALE TRA(-10,10)
The RANDARRAY function
For Microsoft 365 subscribers, the RANDARRAY
function provides a set of random numbers. You can choose the number of rows and columns to fill with numbers. You can also select minimum and maximum values and specify whole or decimal numbers.
The syntax for this function is RANDARRAY(rows, columns, minimum, maximum, whole-decimal)
where you enter True
an integer or False
a decimal number as the last argument. All arguments are optional.
For a random series of numbers spanning three rows and four columns, enter the following and press Enter:
=RANDARRAY(3,4)
For a random array that covers the same number of rows and columns but also has a minimum of 1 and a maximum of 10, enter the following and press Enter:
=RANDARRAY(3,4,1,10)
For a random array that takes these same arguments but returns only integers, enter the following and press Enter:
=RANDARRAY(3,4,1,10,VERO)
Microsoft Excel offers simple options for generating random numbers. Whether you need a decent number or just a number between one and 10, Excel has you covered.