There are a surprising number of ways you can use a random number generator in Excel. But before doing this, it is important to understand the function of a random number generator and how to use it for different applications.
Once you’ve learned how to use the random number functions, you can do things like generate sample dummy data to test calculations, pick the order of players for a game, pick winners for a match, and much more.
Random Number Generator Functions in Excel
The two functions in Excel that work to create random numbers are RAND, RANDBETWEEN, and RANDARRAY.
- EDGE: Returns a random number between 0 and 1. This is usually a decimal, such as 0.358432.
- EDGE BETWEEN: Returns a random number between two numbers you specify. In other words, if you want a random number between 1 and 100, this function returns an integer within that range.
- RANDARRAY: Returns any number of arbitrary integers or decimals, between a minimum or maximum number, and over any range of rows and columns.
Whenever you open a spreadsheet or refresh calculations, both functions generate a new random value. Unfortunately, any change to the spreadsheet will update these values.
Also keep in mind that both random number functions can generate duplicate values when you use them in multiple cells. RANDBETWEEN is more likely to generate duplicates because RAND generates decimals and there are many more possible numbers.
How to use the RAND function
The RAND function is very easy to use because there are no arguments at all. Just type “= RAND()” in a cell and press Enter†
This immediately generates a decimal between 0 and 1.
When to use the RAND function?
How can such a simple function be useful? Here are some practical ideas for using the RAND function.
Suppose you have a list of names of friends who play a board game and you want to quickly determine the order of play. The RAND function is perfect for this.
Just list all the names of your friends in the first column.
Use the RAND() function to generate arbitrary decimals in the next column.
Finally, select both columns, select House select in the menu Sort and filter from the ribbon, then select Custom Sort†
In the Sort window, choose the column with the random numbers to sort and order them out largest to smallest† Select Okay†
This will rearrange your list according to the random numbers generated. In addition, the names will also be used in the new gameplay order.
You can use RAND in the same way in any case where you want to randomize a value in a calculation, a list of text values, or anything else stored in your spreadsheet.
How to use the RANDBETWEEN function
The RANDBETWEEN function is almost as easy as the RAND function to use. Just type “RANDBETWEEN([min]†[max]”, to replace min and max with the lowest and highest numbers of the range you want numbers for.
When you press Enterthat cell will contain a random number between that low or high value.
When to use the RANDBETWEEN function?
The RANDBETWEEN function for generating random numbers is even more useful than the RAND function. This is because it allows you to keep the returned random numbers within a range. It also returns integers instead of decimals.
This gives you some very creative ways to use this feature to apply random assignments when you want to be fair, or assign fixed numbers to people or things without showing any bias.
Let’s look at an example. Let’s say you have a list of 12 people and you want to split them into two teams of 6 each without the embarrassment of having people “choose” their favorite people for each team.
First, create a spreadsheet with all the names of the players.
Add the following function to the next column, next to each player name. Then press Enter†
You can enter this in the first cell and then fill in the rest below. This will automatically assign each player to either team 1 or team 2.
If you notice that the split between the two teams is not even, retype the function in the first cell and press Enter† This will update all cells again. Repeat until the split between the two teams is even.
How to use the RANDARRAY function
The RANDARRAY function is only available in Excel 365. Having this version of Excel is a very powerful random number generator feature in Excel that you can use for a wide variety of purposes.
Its use is very simple, with many optional settings. Just type the following in any cell:
=RANDARRAY ([rows]† [columns]† [min]† [max]† [integer]†
The parameters are as follows:
- rows: Number of rows to generate random numbers
- columns: Number of columns to generate random numbers
- min: Minimum limit for the numbers
- max: Maximum limit for the numbers
- round number: Whether you want decimals or integers (TRUE or FALSE)
If you only use the RANDARRAY function and do not use any of the optional parameters, it works just like the RAND function.
Remember that the RANDARRAY function is very flexible and will only fill in as many rows and columns as you specify, in the exact way you specify. Default is always decimals, and only 1 row and 1 column.
When to use the RANDARRAY function?
The RANDARRAY function is best used when you need a whole collection of random numbers, including multiple columns and multiple rows.
For example, if you simulate a random list of lottery number choices, you can quickly create a list of rows containing 6 columns (for the 6 lottery balls). Set the limit to 69 and let Excel guess your 10 lottery picks for you with the following function:
Place this in the first cell of your spreadsheet and press Enter†
You will see a generated table with 10 rows and 6 columns.
This feature can be a great solution in scientific or statistical research if you need a large random dataset and don’t want to build everything manually.
Choosing the Right Random Number Generator in Excel
Which of the three Excel functions you use depends mainly on the results you are looking for. If you don’t really care what the song is, RAND will do just fine. If you want to limit the range of numbers generated, go with RANDBETWEEN. And if you’re looking for a much larger dataset with a whole table of numbers, RANDARRAY is the best option for you.