How and When to Use It

How and When to Use It:

There are a surprising number of ways to use the random number generator in Excel. But before doing so, it is important to understand the function of a random number generator and how you can use it for different applications.

Once you learn how to use the random number function, you can generate dummy sample data to test calculations, choose the order of players for a game, pick winners for a contest, and many other things. .

Random Number Generator Function in Excel

Two functions in Excel that work to generate random numbers include RAND, RANDBetween, and RANDRE.

  • margin: Returns a random number between 0 and 1. This is usually a decimal like 0.358432.
  • between the shores: Returns a random number between the two numbers you provided. In other words, if you want a random number between 1 and 100, this function will return an integer value inside that range.
  • Randarai:: Returns any number of random integers or decimals between any minimum or maximum number of numbers, and in any range of rows and columns you want.

Both of these functions will generate a new random value each time you open a spreadsheet or refresh calculations. Unfortunately, any change to the spreadsheet triggers an update of these values.

Also, be aware that both of these random number functions can generate duplicate values ​​when you use them multiple cells. It is more likely that randbetween will produce duplicates because rand produces decimals and there are many more possible numbers.

how to use rand function

The rand function is very easy to use because it takes no arguments. Simply type “=RAND()” in a cell and press recorded.

This will immediately generate 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.

Let’s say you have a list of names of friends who play board games and you want to quickly sort out the order of play. The rand function is perfect for this.

Simply list the names of all your friends in the first column.

Use the rand() function to generate a random decimal in the next column.

Finally, select both columns, select House From the menu, choose sort and filter from the ribbon, and then select custom sort.

In the Sort window, select the column containing the random numbers to sort and sort them biggest to smallest. Choose Okay.

This will reorder your list according to the random numbers it has generated. Along with them, it will also resort to names in a new order for the gameplay.

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 stored in your spreadsheet.


The RANDBETWEEN function is as simple as the RAND function to use. just type “randbetween”[min]handjob[max]”, In place of Minute and Max With the lowest and highest number of the range you want the numbers for.

when you press recorded, will be a random number between that low or high value in that cell.

When to Use the RANDBETWEEN Function

The RANDBETWEEN random number generator function is even more useful than the rand function. This is because it lets you keep the returned random numbers within a range. It also returns integer instead of decimal.

This gives you some very creative ways to use this function when you want to be fair, or assign fixed numbers to people or things without 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 that comes from letting people “pick” their favorite people for each team.

First, create a spreadsheet with the names of all the players.

Next to each player’s name, add the following function to the next column. then press recorded.

= randbetween(1,2)

You can enter this in the first cell and then fill in the remainder below. This will automatically assign each player to either Team 1 or Team 2.

If you find that the distribution between the two teams is not even, re-enter the function in the first cell and press recorded. This will update all the cells again. Repeat until the division between the two teams is even.

how to use the render function

Available in RANDARRAY function Excel 365 only. If you have this version of Excel, it is a very powerful random generator function in Excel that you can use for a wide range of purposes.

It is very easy to use with many optional settings. Just type the following in any cell:

= randray([rows]handjob [columns]handjob [min]handjob [max]handjob [integer])

The parameters are as follows:

  • rows: number of rows to generate a random number
  • column: number of columns to generate a random number
  • Minute: minimum limit for numbers
  • Max: max range for numbers
  • Maximum Marks: you want decimal or integer (true or false)

If you just use the RANDARRAY function and don’t use any optional parameters, it will work just like the RAND function.

Just 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. The default is always decimal, and there is only 1 row and 1 column.

when to use the render 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’re simulating something like a random list of lottery number choices, you can quickly create a list of rows that have 6 columns (for 6 lottery balls). Set the limit to 69 and let Excel guess the 10 lottery picks for you with the following function:


Place this in the first cell of your spreadsheet and press recorded.

You will see a generated table consisting of 10 rows and 6 columns.

This function can be a good solution in science or statistical research if you need a large random dataset and don’t want to create it manually.

Choosing the Right Random Number Generator in Excel

whichever of the three excel function Which you use depends mostly on what results you’re looking for. If you don’t really care what the number is, then rand will work fine. If you want to limit the range of numbers generated, go with RANDBETWEEN. And if you are looking for a very large dataset with a complete table of numbers, then RANDARRAY is the best option for you.