random number assignment excel

5 Ways to Generate Random Numbers in Excel

Not every user will have a need for random numbers in Excel. Most people work with fixed numbers and formulas and may have no need for random numbers to appear in their reports.

However, a random number generator does have a huge use when working with different scenarios on a set of data or when performing various statistical analyses.

A financial model may use a stochastic simulation that is dependent on probabilities. The model may need to be run thousands of times, but with the random number generator providing the parameters of each simulation.

Whatever your need for random numbers, Excel has several ways to generate them.

In this post, I’ll show you all the methods you can use to insert random numbers into your workbooks.

Generate Random Numbers with the RAND function

The first way I will show you is the easiest way to generate random values in Excel.

There is a very simple RAND function that requires no parameters and will generate a random number between 0 and 1.

Syntax for the RAND Function

This function has no required or optional arguments. The function is always entered with an empty set of parenthesis.

This function will generate a decimal random number between 0 and 1, but not including 0 or 1.

Repeated values are possible but unlikely since the RAND function produces numbers from a continuous range of numbers.

The values that are returned will follow a uniform distribution. This means that any number between 0 and 1 is equally likely to be returned.

Generate Random Numbers Between Any Two Numbers

A decimal number between 0 and 1 may not be too useful if you need numbers between 1 and 10.

But you can use a simple formula involving the RAND function to generate random numbers between any two numbers.

In general, you can create a random number between X and Y by using the above formula.

For example, to generate numbers between 1 and 10 you can use the above formula.

This multiplies the random number generated by 9 and then adds 1 to it. This will produce decimal numbers between 1 and 10.

Generate Random Integer Numbers Between Any Two Numbers

Another possible need you may encounter is to generate random whole numbers between two given numbers. This can also be done using a simple formula.

In general, you can use the above formula to generate random integer numbers between two values X and Y.

For example, the above formula will create random integer numbers between 1 and 10.

This is the same formula as before, but using the ROUND function to round to zero decimal places.

You can copy this formula down the column on the spreadsheet, and if you keep pressing F9 to re-calculate, you will see various combinations of numbers from 1 to 10.

Since the set of possible numbers is discrete, the random numbers generated may well be duplicated in the list, depending on what the minimum and maximum are of the range.

This also works for producing negative numbers. Suppose you need to generate random integer numbers between -3 and 4, then the above formula will be what you need.

Multiplying the RAND function by 7 will produce random numbers between 0 and 7. Add -3 to the result and round to zero decimal places, and this will give the range of random numbers of -3 to 4.

Generate Random Numbers using the RANDBETWEEN Function

Excel has a useful function for generating random numbers within a range of an upper and lower number.

This is easier to use than using the RAND function as it includes extra operators to arrive at your specific range.

Syntax for the RANDBETWEEN Function

bottom is the lower range for the values to return.
top is the upper range for the values to return.

Both of these arguments are required.

This function will produce random integer numbers between the bottom and top values. This function will also return the upper and lower limits as possible values as it’s not strictly between in this function.

Example with the RANDBETWEEN Function

For example, if you wanted random numbers between -3 and 4, as in the previous example, you can use the above formula.

Note that the RANDBETWEEN function can only produce integer numbers. There is no way of making the function produce decimal numbers. However, it is considerably less complicated than using the RAND function with operators to achieve the same result.

Generate Random Numbers with the RANDARRAY Function

Usually, it’s the case that you don’t want just a single random value but an entire set of random values.

The RANDARRAY function is the perfect solution for this.

It will populate a range of cells with an array of random numbers, which can be very powerful.

This function is only available on the Microsoft 365 version of Excel.

Syntax for the RANDARRAY Function

Rows is the number of rows to return.
Columns is the number of columns to return.
Min is the minimum value for the random numbers.
Max is the maximum value for the random numbers.
Whole_Number is TRUE to return whole numbers, and FALSE to return decimal numbers.

All the arguments are optional for this function.

If no parameters are included, you will get a single random number with decimal places, in the same way as the RAND function.

Example with the RANDARRAY Function

To generate an array of 4 rows and 3 columns of whole random numbers between 6 and 14 you can use the above formula.

This will produce an array of values. Notice the blue border around the numbers? These are all produced from a single formula!

Note that the top left-hand corner of the array is always anchored on the cell that the formula is in. Pressing F9 to recalculate the spreadsheet will change all the numbers in the array.

If you do not put a minimum or maximum value, the default of 0 to 1 will be used.

The minimum value must be less than the maximum value otherwise there will be a #VALUE! error.

The array will automatically resize if you change either the rows or columns parameters in the RANDARRAY formula. This is why they’re known as dynamic arrays.

Warning : If there is already data in one of the cells in the output range that you have entered, you will get a #SPILL! error. No data will be overwritten.

Generate Random Numbers with the Analysis Tools Add-In

There is another method that can be used to insert random numbers without using a formula.

You can use an add-in to create random numbers. Excel comes with an Analysis Tool Pak add-in, but you will need to install it before you can use it.

Install the Analysis Toolpak

Here are the steps to install the Analysis Tool Pak add-in.

Click on the File tab in the ribbon.

In the lower left-hand pane of the window, scroll down and click on Options . You can also use the keyboard shortcut Alt , F , T from the spreadsheet window to open the Options window.

In the left-hand pane of the pop-up window, click on Add-Ins.
At the bottom of the main window displayed, select Excel Add-ins from the dropdown and click on the Go button.

This will display a pop-up window containing all available add-ins for Excel. Check the box for Analysis ToolPak and then click OK .

On the Excel ribbon, on the Data tab, there is now an extra group called Analysis with one button called Data Analysis .

Generate Random Numbers with the Analysis Toolpak

Click on the Data Analysis button in the Analysis group.

This will display a pop-up window. Scroll down and select the Random Number Generation option and then click OK .

A new pop-up window will appear where you can enter your parameters to generate the random numbers.

There are several settings that can be customized.

Number of Variables This is the number of columns of random numbers that you want in your output table. If left blank, then all columns will be filled in the output range that you specify.
Number of Random Numbers This is the number of rows of random numbers that you want to generate. If left blank, the output range that you specify will be filled.
Distribution You can select several distribution methods from the drop-down such as uniform or normal distribution. Different options will become available in the Parameters section depending on your selection here.
Parameters Enter the values to characterize the distribution selected.
Random Seed This is optional and will be the starting point for the algorithm to produce the random numbers. If you use the same seed again, it will produce the same random numbers. If left blank, it will take the seed value from the timer event.
Output Range Enter the upper left cell of where the table is to be constructed in the spreadsheet. If you have left the Variables parameter blank, then you will need to specify an entire range. Note that existing data in that range will be overwritten.
New Worksheet Ply This option will insert a new worksheet within the workbook and will paste the results at Cell A1. Enter a sheet name in the adjacent box, otherwise, a default name will be used.
New Workbook This will create a new workbook and paste the results into cell A1 in the first sheet.

Press the OK button and Excel will insert the random number according to the selected options.

Notice that unlike the formula methods previously shown, these numbers are hardcoded and will not change when you refresh calculations in the workbook.

Generate Random Numbers with VBA

VBA (Visual Basic for Applications) is the programming language that sits behind the front end of Excel, and this can also be used to generate random numbers.

However, it is more complicated than simply entering a formula into a cell in Excel, and you do need some programming knowledge to use it.

To open the VBA editor, use the Alt + F11 keyboard shortcut.

In the left-hand pane of the window (Project Explorer), you will see the workbooks that are open (including add-ins) and the sheets available.

On the menu at the top of the window, click on Insert and then click on Module . This will add a module window to the current spreadsheet. Paste or add the following code to the module.

Press F5 to run this, and a message pop-up will appear in Excel with a random number displayed. Press OK and you will return to the code window.

Run the code again and a different random number will be displayed. The random number will be between 0 and 1, but will not include the values of 0 or 1.

You can also give the Rnd function a parameter, which is a seed for the starting point of the algorithm to produce the random numbers.

If the seed value is set to a negative number or zero, then the same random number will be displayed each time.

Using VBA functions, you can emulate all the functionality of the front-end methods that have been covered in this article.

For example, if you wanted to generate whole random numbers between 3 and 10, then you would use the following above code.

This code multiplies the random number, by 7, and then adds 3 to it, and then rounds to zero decimal places.

Suppose that you then wanted to display your random numbers in the grid. You can do this with the following code.

This code uses a For Next loop to iterate 5 times through the random number calculation and enter the results in a column of cells starting at cell A1.

Remember that any data already there will be overwritten, and there is no warning or undo feature available. Save any previous work beforehand!

There is also a VBA function called Randomize . You can use this before the Rnd function to reset the seed value to the timer event, or to any parameter given.

Generate Random Numbers without Duplicates or Repeats

You may well have a situation where you want to generate a range of random numbers, but you do not want to see any duplicate values appearing.

You may want to select 3 random numbers between the numbers from 1 to 10, but where each of the 3 selected numbers is unique.

You could generate random numbers with the RANDBETWEEN function and then use the Excel function Remove Duplicates from the ribbon, but this still may not give you all the numbers required.

There are several possible solutions available.

Solution with RANK.EQ and COUNTIF Functions

If you don’t have access to the RANDARRAY function in Excel, then you can use a combination of RANK.EQ and COUNTIF to get unique random numbers.

You can create your random numbers using RANDBETWEEN and then use a formula in the next column to rank them thereby giving you a randomly sorted sequence from 1 to 10.

In cell B2, enter the above formula . Copy this formula down so that there are 10 rows of random numbers going down to cell B11.

You will notice that some numbers may be duplicated and some are not shown at all.

You can then use the RANK.EQ function to rank them so as to create a sequence from 1 to 10 but that is sorted randomly.

In cell C2, enter the above formula.

Note that there are absolute references used (the $ signs) so that formula references stay fixed as you copy the formula down.

Copy this formula down to cell C11, and this will display all the numbers between 1 and 10, but in random order.

To explain this formula in more depth, it uses two functions RANK.EQ and COUNTIF .

Number is the number that we want to find the rank of in the array.
Ref is the array where we want to search for the number.
Order is optional and allows you to find the rank in either ascending or descending order. If its omitted then ascending order is used.

The RANK.EQ function returns the rank of a number within an array of numbers.

Range is the range that is being searched for instances of the criteria.
Criteria is the value to match within the range.

The COUNTIF function counts the number of cells based on a given criterion. In this case, it is counting how many times a given random number has appeared in the list.

For each random number the RANK.EQ function will determine its ranking position relative to the other random numbers. But if the random numbers contain duplicates, then they will create a tied ranking.

The COUNTIF function will compensate for any ties in the ranking and will add one to the rank for each time the random number has previously appeared.

This creates a unique ranking where ties don’t get the same rank.

Since this rank is based on a set of random numbers the result is the same as randomizing a list of numbers from 1 to 10.

Now, if you only want 5 non-repeating numbers, you only need to take the first 5 from the ranking list.

Solution with VBA

You could also use VBA to generate a string of random numbers from 1 to 10 without duplicates.

This code iterates through values from 1 to 5, generating a random number between 1 and 10 each time.

It tests the random number to check if it has already been generated. This is done by concatenating successful numbers into a string and then searching that string to see if the number has already been used.

If it has been found, then it uses the label Repeat to go back and re-generate a new number. This is again tested that it has not already been used. If it is a new number, then it is added to the sheet.

Solution with Dynamic Arrays

If you have dynamic arrays in Excel, then there is a single formula method to avoid repeating values.

Suppose you want to return 5 numbers from the sequence 1 to 10. You want each number selected to be unique.

This can be done using a combination of the SEQUENCE , SORTBY , RANDARRAY , and INDEX functions.

The above formula creates a sequence of numbers from 1 to 10.

It then sorts them in a random order using the SORTBY function and sorting on a column of random numbers generated by the RANDARRAY function. The effect is to sort the sequence in random order.

Now if you want to get 5 random and unique numbers you only need to take the first 5 numbers from the randomly sorted sequence.

This is exactly what the INDEX function does! This part of the formula will return the first 5 numbers from the randomly sorted sequence.

There are several ways to generate random numbers in Excel.

Whether you need whole numbers, decimals, or a range of random numbers with an upper and lower limit, the facility is available. Excel is extremely versatile on this topic.

However, bear in mind that these numbers are pseudo-random numbers generated by an algorithm.

Although the random number generator passes all the tests of randomness, they are not true random numbers.

To be a truly random number, it would have to be driven by a random event happening outside the computer environment.

For most purposes of constructing general simulations and statistical analysis, the Excel random number generator is considered fit for the purpose.

Have you used any of these methods for generating random numbers in Excel? Do you know any other methods? Let me know in the comments below!

About the Author

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

Pivot Table Tips and Tricks You Need to Know
Everything You Need to Know About Excel Tables
The Complete Guide to Power Query
Introduction To Power Query M Code
The Complete List of Keyboard Shortcuts in Microsoft Excel
The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

7 Ways To Recover Unsaved Microsoft Excel Files

Jul 8, 2024

Are you wondering how to recover unsaved Excel files? Read this ultimate Excel...

8 Ways To Collapse Rows in Excel

Jul 6, 2024

This effortless and detailed Excel tutorial will show you how to collapse rows...

4 Ways To Calculate Days Between Dates in Microsoft Excel

Jul 4, 2024

In this effortless Excel tutorial, I'll show you how to calculate days between...

Get the Latest Microsoft Excel Tips

How to Generate Random Numbers in Excel (Without Duplicates)

Randomness is often just as necessary as order and symmetry, whether you are dealing with life or an Excel worksheet. Sometimes, you need it to ensure every value has an equal opportunity, like in a lottery.

The concept of ‘chance’ also plays an important role in simulating complex systems. It helps you test data. We often use random numbers to generate sample datasets for our tutorials too!

The good news is, Excel contains some very useful functions to help generate random numbers. The bad news is that these functions don’t always promise a unique set of random numbers.

If you look up ‘ How to Generate Random Numbers without Duplicates in Excel ’, you’ll probably find at least 10 different ways to do it.

Most of them work quite well too. However, we’ve noticed most tutorials leave you with a really long formula and hardly any explanation on how it works.

In this tutorial, we will show you 2 different ways in which you can generate unique random numbers in Excel using formulas.

Table of Contents

Random Number Generating Functions in Excel

There are mainly 2 random number generating functions available in Excel. Let us see them one by one:

RANDBETWEEN Formula

The RANDBETWEEN function is a great function that helps you generate random whole numbers between an upper and lower limit.

The syntax for this function is:

In the above formula, you can specify the lower and the upper limit. For example, if the limits are 10 and 1000, this formula will generate random numbers between these two specified limits.

This method most likely produces unique results if you have a larger range and need to generate a small number of random numbers.

For example, if you need to generate 10 numbers in the range of 10 to 1000. However, this is not the most reliable method for getting unique random numbers.

RAND Formula

The RAND function returns a decimal random number between 0 and 1. It takes no arguments, so its syntax is just

The great thing about this function is that there is a good probability that the numbers generated by it will not repeat, at least for 100,000 times.

The main differences between RAND() and RANDBETWEEN() are :

RANDBETWEEN returns a whole number while RAND returns a decimal number
RANDBETWEEN needs two parameters that specify the range in which the random numbers will be generated. RAND does not take any parameters. It can generate any number between 0 and 1.
RANDBETWEEN is less likely than RAND to generate unique random numbers.

Both RANDBETWEEN and RAND recalculate values every time the worksheet gets updated.

That means you get a new random number every time you change any cell value in the worksheet or press the F9 key .

Both these numbers fail to provide truly random numbers if used on their own, but combining them with other functions, gives a high probability of getting random numbers without repetition. Let us see how.

Using RAND and RANK to get Random Numbers in Excel Without Duplicates

You can combine the RAND and RANK functions to get a set of random numbers without duplicates.

The RANK function returns the rank of a number within a set of numbers.

Its syntax is as follows:

The first parameter is the number or reference that you want to rank.

The second parameter is the set of numbers or range of cells that you will rank from.

The third parameter is optional. It specifies whether you want to rank in ascending or descending order. If it’s 0, the numbers are ranked in descending order. If otherwise the numbers are ranked in ascending order. By default, the numbers are ranked in descending order.

Let’s say you want 10 random numbers from A2 to A11. Here’s how you can combine the two functions:

Uisng the RAND function to generate random number

In the adjacent column (B), use the RANK formula as follows: =RANK(A2, $A$2:$A$11). The second parameter is a range of absolute cell locations. These make sure the cell references do not change when the formula is copied to other cells.
Press the Return Key.

Unique random numbers without duplicates

Now, these numbers are volatile at the moment, since the RAND() function always recalculates every time there’s a change in the worksheet. To avoid that, copy the cells (B2:B11) and paste them in the same place as value.
Delete the first column (A).

This should give you a list of 10 random numbers, and that too without any repetition.

All we did here is get a random set of decimal numbers between 0 and 1 and then rank these numbers from 1 to 10 to get unique random integers.

Thus, we got a nice random set of numbers between 1 and 10 in column B (before deleting Column A).

This method should give you completely random and unique numbers with 99% assurance. But there might be slight discrepancies if you have to generate a much larger set of numbers.

For this, you can use the Remove Duplicates option to get rid of any duplicates.

Select the range of cells containing your random set of numbers.

From the Data tab, select Remove Duplicates from the Data Tools group.

Using RAND, LARGE and MATCH to get Random Numbers in Excel Without Duplicates

A similar method is to use RAND in combination with LARGE and MATCH functions. The LARGE function returns the kth largest value in a list, for example, the fourth largest value or the fifth largest value, etc. The syntax for this function is

Here, the first parameter is the set of numbers or range of cells in which you want to find the largest

The second parameter is an integer that specifies the position from the largest value, i.e. the kth position.

The MATCH function looks for an item in a range of cells and then returns the relative position of that item. The syntax for this function is:

Here, the first parameter is the value you are looking for.

The second parameter is the set from which you want to find the value’s position

The third parameter is optional. It is basically used to specify the type of matching that should be done (the largest value that is less than, the smallest value that is greater than or exact match).

Let’s say you want 10 random numbers from A2 to A10. Here’s how you can combine the three functions:

In the first cell (A2), type: =RAND(). This will generate a random number between 0 and 1.
Pull down the fill handle (located at the bottom right corner of the cell) to copy the formula to as many cells as you need. So if you want 10 random numbers, copy it down to cell A11.
In the adjacent column (B), use the LARGE formula as follows: =LARGE($A$2:$A$11,ROW(A1)). The first parameter is a range of absolute cell locations. These make sure the cell references do not change when the formula is copied to other cells. Here, we are trying to find the 1st largest value from A2 to A11. ROW(A1) means the row number of A1, in other words, the number 1. This formula could also have been written as: =LARGE($A$2:$A$11,1). But since we want the value to change when it is copied in each row, we used ROW(A1) instead.
Press the Return Key
Drag down the fill handle for the cell (B2) or just double click it.
In the next column (C), use the MATCH function as follows: =MATCH(B2,$A$2:$A$11,0). Here, we are trying to find the position of the value in B2 in the range A2 to A11. We want an exact match, so we specify the third parameter as 0.
Drag down the fill handle for the cell (C2) or just double click it.
These numbers are volatile at the moment since the RAND() function always recalculates every time there’s a change in the worksheet. To avoid that, copy the cells (C2:C11) and paste them in the same place as value.
Delete the first two columns (A and B).

This should give you a list of 10 random numbers without any repetition.

In this method, we got a random set of decimal numbers between 0 and 1 in column A and then arranged them in descending order in column B.

Next, we found the position of each number from column B in the set of random numbers of column A. This gave us a random set of numbers between 1 and 10 in column C (before deleting Columns A and B).

All we did here was convert the random decimal numbers to their integer counterparts.

In this tutorial, we showed you two ways in which you can generate random numbers in Excel without repetition.

There are plenty of other ways to do this. We leave it up to you to decide which way you find the easiest and most effective.

Other Excel tutorials you may find useful:

How to Reverse a Text String in Excel (Using Formula & VBA)
How to Highlight Every Other Row in Excel
How to Format Phone Numbers in Excel
How to Generate Random Names in Excel
How to Convert Decimal to Fraction in Excel
Excel Showing Formula Instead of Result (How to FIX!)
Get Unique Values from a Column in Excel

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

RAND function

This article describes the formula syntax and usage of the RAND function in Microsoft Excel.

Description

RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

The RAND function syntax has no arguments.

To generate a random real number between a and b, use:

=RAND()*(b-a)+a

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. The formula will calculate and leave you with just a value.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2 , and then press Enter . You can adjust the column widths to see all the data, if needed.


=RAND()	A random number greater than or equal to 0 and less than 1	varies
=RAND()*100	A random number greater than or equal to 0 and less than 100	varies
=INT(RAND()*100)	A random whole number greater than or equal to 0 and less than 100	varies
	When a worksheet is recalculated by entering a formula or data in a different cell, or by manually recalculating (press ), a new random number is generated for any formula that uses the RAND function.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities .

Mersenne Twister algorithm

RANDBETWEEN function

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to Generate Random Numbers in Excel

There may be cases when you need to generate random numbers in Excel.

For example, to select random winners from a list or to get a random list of numbers for data analysis or to create random groups of students in class.

In this tutorial, you will learn how to generate random numbers in Excel (with and without repetitions).

Generate Random Numbers in Excel

There are two worksheet functions that are meant to generate random numbers in Excel: RAND and RANDBETWEEN.

RANDBETWEEN function would give you the random numbers, but there is a high possibility of repeats in the result.
RAND function is more likely to give you a result without repetitions. However, it only gives random numbers between 0 and 1. It can be used with RANK to generate unique random numbers in Excel (as shown later in this tutorial).

Generate Random Numbers using RANDBETWEEN function in Excel

Excel RANDBETWEEN function generates a set of integer random numbers between the two specified numbers.

RANDBETWEEN function takes two arguments – the Bottom value and the top value. It will give you an integer number between the two specified numbers only.

For example, suppose I want to generate 10 random numbers between 1 and 100.

Here are the steps to generate random numbers using RANDBETWEEN:

Select the cell in which you want to get the random numbers.
In the active cell, enter =RANDBETWEEN(1,100).
Hold the Control key and Press Enter.

This will instantly give me 10 random numbers in the selected cells.

Generate Random Numbers in Excel - Randbetween

While RANDBETWEEN makes it easy to get integers between the specified numbers, there is a high chance of repetition in the result.

For example, when I use the RANDBETWEEN function to get 10 random numbers and use the formula =RANDBETWEEN(1,10), it gives me a couple of duplicates.

Generate Random Numbers in Excel - Randbetween repetition

If you’re OK with duplicates, RANDBETWEEN is the easiest way to generate random numbers in Excel.

Note that RANDBETWEEN is a volatile function and recalculates every time there is a change in the worksheet. To avoid getting the random numbers recalculate, again and again, convert the result of the formula to values .

Generate Unique Random Numbers using RAND and RANK function in Excel

I tested the RAND function multiple times and didn’t find duplicate values. But as a caution, I recommend you check for duplicate values when you use this function.

Suppose I want to generate 10 random numbers in Excel (without repeats).

Here are the steps to generate random numbers in Excel without repetition:

Select the cells in which you want to get the random numbers.
In the active cell, enter =RAND()

Select all the cell (where you have the result of the RAND function) and convert it to values .

Generate Random Numbers in Excel - Rank Function

Now you can use the values in column B as the random numbers.

Note: RAND is a volatile formula and would recalculate every time there is any change in the worksheet. Make sure you have converted all the RAND function results to values.

Caution: While I checked and didn’t find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.

You May Also Like the Following Excel Tutorials:

Automatically Sort Data in Alphabetical Order using Formula .
Random Group Generator Template
How to Shuffle a List of Items/Names in Excel?
How to Do Factorial (!) in Excel (FACT function)

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

3 thoughts on “How to Generate Random Numbers in Excel”

The link or the Ebooks do not send to my email

Just a trick, RAND and RANDBETWEEN are volatile functions, so each time there’s a change they will be updated. To avoid this, you can create a Pivot Table referencing the cells with the random values (functions) and then reference the Pivot Table values. This way the random values won’t change until the Pivot Table is updated.

How to Generate Random Numbers in Excel: RAND & RANDBETWEEN

Randomness in numbers means a set of numbers that simply lacks a pattern.

Random numbers have a great application in practical life. Starting from the field of auditing (to pick random samples), to statistics, to sciences, and so on 👩‍🏫

Psychologically speaking, a human brain can never pick truly random numbers. We are always biased.

And that is where we need machines to intervene. Excel has a variety of functions to generate random numbers. Two of them are the RAND and RANDBETWEEN functions.

To learn all about these functions – glide right into the guide below.

Table of Contents

Random numbers with RAND

Random numbers with randbetween, frequently asked questions.

RAND is the most basic function to generate unique random numbers in Excel. The easiest and the simplest.

To talk about the arguments of this function, it does not argue. In other words, it has no argument 💁‍♀️ Funny, no?

The RAND function of Excel generates random decimal numbers that fall between 0 and 1.

The RAND function is available in all versions of Excel. Starting from Excel 2000 to Excel 365.

To find a random number in Excel (and it could be any random number ranging from 0 to 1), write the RAND function as follows:

Hit “Enter” and that’s it.

You get a random number. That’s how easy it is to create random numbers in Excel 🤩

If you want to generate a series of random numbers, copy this formula and paste it into multiple cells.

Or drag and drop it to as many cells (on the bottom, or the right) as many random numbers are needed 📍

Here, we have copied this formula to 4 cells on the bottom.

And there we have 4 random numbers generated in Excel.

The RANDBETWEEN is a very cool function of Excel. It generates a random number (or a series of random numbers) within a specified range 😎

However, note that the RANDBETWEEN function only generates random integers. Not decimal numbers.

The RANDBETWEEN function is only available in Excel 2007 version and onwards.

To generate a random number in Excel between 0 and 100:

Select the cell where you want the random number populated.
Write the RANDBETWEEN function as follows:

= RANDBETWEEN (0,100)

In the above function, 0 is the bottom and 100 is the top.

This tells Excel to generate a random number that falls between 0 to 100. In other words, an integer that is equal to or greater than 0 but smaller or equal to 100 ✌

Hit “Enter” and there you go.

That’s how you can create random integer numbers in excel.

Need more of them?

4. Drag and drop the cell down across as many cells as many values are needed.

For example, we want 4 more such random numbers so dragging it 4 cells down⬇

Similarly, you can drag this list to the right or left of your cells to generate as many random numbers, as needed.

For example, here we have dragged it 4 columns to the right ➡

The random number functions of Excel are volatile 🔥

They recalculate every time any action is performed on your spreadsheet. This action needs not to be something about the random number functions. It could be anything.

For example, if you applied the RAND function and got a random number 81. If the sheet is recalculated, the RAND function will recalculate and return another random number. Say 97.

Here’s how you can stop these functions from recalculating over and over again 🚴‍♀️

Click on the cell where the function is written. Go to the formula bar and press the F9 key . This converts the function into its value. This means 81 is now a general value in Excel. It won’t change with new recalculations now.
If you have many random numbers in your Excel sheet, copy them. And paste them as values using Past Special > Values .

That’s it – Now what?

The guide above teaches different ways of generating random numbers in Excel. You can generate single or multiple random decimal numbers using the RAND function 🔢

And with the RANDBETWEEN function, you can land on a random integer between a specified minimum and maximum limit. How cool is that?

If you enjoyed learning about these functions in Excel, let me tell you there’s so much more to learn about Excel.

Excel’s function library is a very vast one. To begin learning, I recommend you go with the VLOOKUP, SUMIF, and IF functions of Excel.

How? Enroll in my 30-minute free email course here that teaches you all about these functions.

Other resources

In addition to generating random numbers in Excel, you might simply want to randomize an already created list in Excel.

Or you may want or sort a randomized list in Excel. Well, for both of the said cases, we have got you covered.

Read our other blogs to learn how to do that in Excel.

How do I generate multiple random numbers in Excel?

To generate multiple random numbers in Excel, apply the =RAND() function in any cell of Excel.

Copy and paste or drag and drop this formula to multiple cells to get multiple random numbers in Excel.

How do I generate a random number between 1 to 100 in Excel?

Use the RANDBETWEEN function to generate a random number in Excel between any two given numbers.

For random numbers between 1 to 100, write the RANDBETWEEN function as follows:

Excel will return any random number between 0 and 100.

List Your Business in Our Directory Now! →

Business Directory

How to Generate Random Numbers in Excel

Written by: Bill Whitman

Last updated: May 20, 2023

Are you looking for a quick and easy way to generate random numbers in Microsoft Excel? Look no further. As an expert in Microsoft Excel, I can provide you with a straightforward solution. Excel offers a variety of functions that can help you generate random numbers with ease, including the RAND function. In this blog post, I will walk you through the process of generating random numbers in Excel, including how to use the RAND function and how to apply it to your specific needs. Let’s get started!

Understanding Randomness in Excel

Before diving into generating random numbers in Excel, it’s important to understand what “random” means in this context. In Excel, randomness refers to the chance that any number within a range could be selected. Excel offers a variety of functions that provide different types of randomness, such as uniform distribution, normal distribution, and more. For the purpose of this tutorial, we will focus on generating random numbers using the RAND function.

Using the RAND Function

The easiest way to generate random numbers in Excel is by using the RAND function. The RAND function generates a random decimal number between 0 and 1, with each value equally likely to be chosen. Here’s how to use the RAND function:

Step 1: Select a Cell

Start by selecting the cell where you want to generate the random number.

Step 2: Enter the RAND Function

Next, type “=RAND()” into the selected cell. Press enter, and a random decimal number between 0 and 1 will appear in the cell.

Step 3: Copy and Paste the RAND Function

To generate additional random numbers using the RAND function, simply copy and paste the function into additional cells. Each time the function is pasted into a new cell, a new random decimal number will be generated.

Generating Whole Numbers using the INT Function

If you need to generate whole numbers instead of decimals, you can use the INT function in conjunction with the RAND function. Here’s how to do it:

Step 2: Enter the INT Function

Type “=INT(RAND()*n)” into the selected cell, where “n” represents the highest number you want to generate. For example, if you want to generate random numbers between 1 and 10, you would enter “=INT(RAND()*10)+1”.

Step 3: Copy and Paste the Formula

As with the RAND function, you can copy and paste the formula into additional cells to generate more random whole numbers.

Generating random numbers in Excel doesn’t have to be difficult. With the RAND and INT functions, you can quickly generate random decimal or whole numbers for a variety of purposes. Try using these functions the next time you need to generate random numbers in Excel.

Using the RANDBETWEEN Function

If you’re looking for a quick way to generate random whole numbers within a specific range, you can use the RANDBETWEEN function. This function allows you to specify a range of whole numbers, and Excel will choose a random number within that range. Here’s how to use the RANDBETWEEN function:

Step 2: Enter the RANDBETWEEN Function

Type “=RANDBETWEEN(x,y)” into the selected cell, where “x” represents the lowest number in the range and “y” represents the highest number in the range. For example, if you want to generate a random number between 1 and 10, you would enter “=RANDBETWEEN(1,10)”.

As with the other functions, you can copy and paste the formula into additional cells to generate more random whole numbers in the same range.

Generating Random Dates and Times

In addition to generating random numbers, Excel can also generate random dates and times. This is a useful feature for creating mock data sets or testing formulas. Here’s how to generate random dates and times in Excel:

Start by selecting the cell where you want to generate the random date or time.

Step 2: Enter the RAND Function for Dates and Times

To generate a random date, enter “=RAND()*365+DATE(2000,1,1)” into the selected cell. This will generate a date between January 1, 2000 and December 31, 2000. To generate a random time, enter “=RAND()” into the selected cell. This will generate a decimal number between 0 and 1, which you can format as a time.

As with the other functions, you can copy and paste the formula into additional cells to generate more random dates or times.

Excel offers a wide range of functions for generating random numbers, dates, and times. By using the RAND, INT, and RANDBETWEEN functions, you can quickly and easily generate random data sets for a variety of purposes. Whether you’re working with large data sets or simply need a quick and easy way to generate random numbers, Excel has you covered.

Here are some frequently asked questions about generating random numbers in Excel:

Can I generate random numbers without decimals?

Yes, you can use the INT function in combination with the RAND function to generate whole numbers. Simply multiply the RAND function by the highest number you want to generate, and wrap the whole formula in the INT function. For example, to generate random whole numbers between 1 and 5, you could use the formula “=INT(RAND()*5)+1”.

What’s the difference between the RAND and RANDBETWEEN functions?

The RAND function generates a random decimal number between 0 and 1, while the RANDBETWEEN function generates a random whole number within a specified range. The RANDBETWEEN function is useful when you need to generate whole numbers within a specific range.

Can I generate random dates and times in Excel?

Yes, Excel offers several functions that allow you to generate random dates and times. The RAND function can be used to generate random decimal numbers, which you can then format as dates or times. The RANDBETWEEN function can also be used to generate random dates within a specific range.

Can I generate a list of unique random numbers in Excel?

Yes, you can use the RAND function in combination with the RANK function to generate a list of unique random numbers. First, enter the RAND function into each cell in a column. Then, use the RANK function to rank the values in the column as either ascending or descending. This will give you a list of unique random numbers.

Can I generate random numbers based on certain criteria?

Yes, you can use the IF function in combination with the RAND function to generate random numbers based on certain criteria. For example, if you wanted to generate random numbers between 1 and 10 only for cells that contain the text “Dog”, you could use the formula “=IF(A1=”Dog”,RAND()*10,””)”. This would generate a random number only for cells that contain the text “Dog”.

Featured Companies

Learn powerpoint.

Explore the world of Microsoft PowerPoint with LearnPowerpoint.io, where we provide tailored tutorials and valuable tips to transform your presentation skills and clarify PowerPoint for enthusiasts and professionals alike.

Your ultimate guide to mastering Microsoft Word! Dive into our extensive collection of tutorials and tips designed to make Word simple and effective for users of all skill levels.

Resultris Marketing

Boost your brand's online presence with Resultris Content Marketing Subscriptions. Enjoy high-quality, on-demand content marketing services to grow your business.

How to Use Structured References in Excel

How to Remove Drop-down in Excel

How to Group Worksheets in Excel

How to Recover Excel File Not Saved

How to Remove Subtotals in Excel

How to Unlock a Cell in Excel

How to Calculate Hours Worked in Excel

How to Calculate Covariance in Excel

How to Add Dates in Excel

Generate Random Numbers in Excel (5 Easy Ways + VBA)

This tutorial covers generating random numbers in Excel. Using the RAND, RANDBETWEEN and RANDARRAY functions, and VBA, we will demonstrate how to generate random whole numbers and decimals. We’ve also got you covered on generating random numbers without repeat values.

Why is randomizing important? Randomizing gives a fair and equal chance of selection to all units. For picking out a student or employee based on their role number or employee code, for picking lottery tickets, to set a random order, statistical analysis. All these examples require random number selections.

Excel hands us many ways of generating random numbers, some easy ones we’ll share with you today.

Let’s get generating! (Or randomizing! Or numbering!)

Table of Contents

Method #1 – Using RAND Function

The RAND function returns a random decimal between 0 and 1. It is a volatile function and the numbers are recalculated every time the worksheet is recalculated.

As mentioned, the RAND function will return decimals, not integers but will generate random numbers nonetheless. Here’s the formula to use the RAND function to generate random decimals between 0 and 1:

The RAND function is singularly entered without any arguments and instantly returns a random decimal. The Fill Handle can then be used to copy the formula and have as many random numbers generated as needed.

Since RAND is a volatile function, you will have to copy-paste the values to keep the values in place.

Given the many digits of these decimals, the RAND function is highly likely to return unique numbers.

A small addition to the RAND function can get RAND to generate random integers, or at least display them as integers. Let’s get a set of random numbers with this formula:

The syntax becomes:

11 comes from deducting the minimum value from the maximum value. Since we want random numbers between 1 and 12, 12-1=11. Then the minimum value is added at the end of the formula.

If you want to include your minimum and maximum values in the generations, add 1 to your maximum value and subtract 1 from your minimum value.

The following are the results of this formula:

Now using the Decrease Decimal button in the Home tab’s Number group, reduce the decimals until you are left with integers.

The ROUND function can be used (instead of decreasing decimals) with this formula to deliver integers.

On its own for generating random numbers, it might not be what you’re looking for. Paired with other functions, it can generate random integers with and without repetitions. We have a couple of examples ahead.

Method #2 – Using RANDBETWEEN Function

The RANDBETWEEN function returns a random number between the two numbers you specify. The very first thing to note about RANDBETWEEN is that it will generate repeat values. Let’s say we want 12 random numbers from the sequence of 1 to 12, this is the formula we will use:

The RANDBETWEEN function only needs the first and last values of the range that you want the random numbers from. With 1 and 12 as the bottom and top values, this is an example of the random values it returns:

Evidently, there are many repetitions of values. One way around this can be to substantially expand the range supplied to the function to decrease the chances of duplicates. Let’s try 1 to 1000 and see how the RANDBETWEEN function responds to that:

RANDBETWEEN is also a volatile function. For the values to stay, paste the values onto themselves.

By the look of it, we may not have repeated values. A surefire check can be performed using the Remove Duplicates feature. Select the values you want to check for duplications, paste the values onto themselves and then go to the Data tab > Data Tools group > Remove Duplicates button:

Select the Continue with the current selection radio button in the Remove Duplicates Warning dialog window.

Then select the Remove Duplicates button.

Click on the OK command button in the next window:

If there are no duplicates, you will see a small window confirming so:

If there are duplicates, you can have them removed using this feature and keep the values left over.

Method #3 – Using RANDARRAY Function

The RANDARRAY function returns an array of random numbers. The range between which the random numbers are to be returned, the number of random generations, decimals, or whole numbers, all can be defined.

However, like RANDBETWEEN, it returns duplicate values so all the tips of expanding the range or using RANDARRAY with other functions to avoid duplicates, apply here too.

Let’s see the simple application of the RANDARRAY function to generate random numbers:

All the arguments are optional and if all of them are left out, RANDARRAY returns one random decimal between 0 and 1 (like the RANDBETWEEN function).

In our formula, the first 12 is the number of rows to be filled by the function followed by the number of columns, which we have specified as 1. The next two values (1,12) define the range for random number generations. TRUE returns integers and FALSE or omission of this argument returns decimals.

The results spill to the specified range so you don’t need to copy the formula down. Here’s what our formula looks like on the sheet:

Method #4 – Using RAND & RANK Functions

The RANK function returns the rank of a certain number in a list of numbers. This can make the RAND function more useful for random number generation. The RAND function will return random decimals and the rank function will rank the numbers according to their values. This will result in a set of random numbers without duplications.

Here’s what to do to generate a list of random numbers with the RAND and RANK functions:

Apply the RAND function as follows and copy the formula down to as many cells of random numbers as required.

The RAND function is used plainly to return a list or random decimals between the numbers 0 and 1. For our example, we require 12 random numbers so we have filled the formula down to eleven more cells:

In a new column, enter the following formula:

The RANK function will define the rank of a decimal generated by the RAND function. RANK is required to return the rank of cell B3 in the range B3:B14. The range is locked by dollar signs to avoid the repetition of numbers. The numbers will be repeated because as the formula moves down, the range will also move down, leaving out the decimals from the top and defining the rank of the specified cell in a new range every time.

To lock the cells into absolute references , press the F4 key with the blinking text cursor positioned after the cell reference in the formula bar.

The third optional argument of the RANK function is the order by which the function is supposed to rank the numbers. If left out, the function by default ranks the numbers by descending values; the first being the largest number.

Drag the formula down to C14 to get all the numbers ranked and to get a list of 12 numbers at random.

You can see below that the number ranked as 1 is the largest number in the range B3:B14 and the one ranked as 12 is the smallest:

At this point, the results of the RAND function are volatile, making the results of the RANK function volatile too. It’s a good idea to leave it volatile until this point so you can recalculate the numbers if you don’t like the order.

Once you have the results of the RANK function, copy the C column and paste the values on themselves to avoid the values recalculating and to avoid reference errors when column B is deleted.
Delete the column with the RAND function.

Method #5 – Using RAND, LARGE & MATCH Functions

The LARGE function returns the nth largest value in a dataset. The MATCH function is a lookup function that returns the position of a lookup value in an array. The RAND, LARGE, and MATCH can be used to return a random set of numbers, similar to using the RAND and RANK functions.

The RAND function will be used to generate random decimals that the LARGE function will arrange in descending order. Then the MATCH function will be used to look up each value from the LARGE function to result in its position in the list of decimals from the RAND function.

This is how to use the RAND, LARGE, and MATCH functions to generate a random set of numbers without repetitions:

The first step again is to use the RAND function with no arguments for returning random decimals:

Enter the following formula with the LARGE function:

The LARGE function takes the values in the range B3:B14 generated from the RAND function. This range is an absolute reference in the formula so that no value is missed out as the formula is copied down.

The second argument of the function is N in terms of defining the Nth value. Since we want this value to change in sequence as the formula is copied down, we have mentioned the ROW function with cell A1. This could be any column (e.g. B1, C1, or D1) as the ROW function will only deal with the row number. We have started with 1 so that the formula can change to row 2,3,4, so on as the formula will be filled down.

For the first instance, the ROW function returns the number 1 with A1 as the argument. That means the LARGE function has to find the 1st largest value in the range B3:B14.

In the second instance, the argument of the ROW function will change from A1 to A2. LARGE is to find the 2nd largest value in B3:14. That’s how this formula will work all the way down to the 12 values and it arranges all the values from the RAND function in descending order.

Next, use the following formula in a new column:

Now the MATCH function will look up the position of the value of cell C3 in the mentioned array B3:B14. The zero at the end is used by the MATCH function to find an exact match. As the MATCH function keeps finding the position of each value in B3:B14, it will keep returning the positions, supplying us with a list of random numbers.

Copy-paste the values of the MATCH functions onto themselves to prevent reference errors when the other two columns are deleted.

Delete the columns with the RAND and LARGE functions.

Method #6 – Using VBA

If you’d rather be interested in the works of coding, many codes can generate random numbers. Decimals or integers, pop-ups or lists, single or multiple values. You only need the relevant code. That code will work through VBA to generate random numbers.

As with all random generations, the question is – Will there be any repeat values? Yes and no; we have two codes for you here, one of them returns duplicates and one doesn’t. These codes can be edited to your requirements with minimum and maximum values and the number of random generations.

From all the types of VBA random number generations, we will show you how to get a list of 20 integers on your worksheet. Without further ado, let’s get into the steps of generating random numbers using VBA :

Go to the Developer tab > Code group > Visual Basic button to open the VB editor. If you don’t have the Developer tab showing on the ribbon, you can press the Alt + F11 keys. This is the VB editor:

Select the Module option from the Insert tab’s menu. This will open a Module window for feeding the code.

Here’s the Module window:

With Duplicates

If duplicate values are not a problem, go with this code:

Copy-paste the following code into the Module window:

The function sets the values to return as whole numbers:

Close the VB. Now you should be back to your worksheet.
Enter this formula in the cell where you want the first random number:

This formula connects with the code fed through the VB editor. 1 and 20 are the bottom and top values between which you want the random number of generations. 0 is the number of decimal places you want in the generated values.

Here are the 20 random numbers generated using the VBA code and the formula mentioned above:

Without Duplicates

For unique random numbers, enter this code instead:

In the Module window in the VB editor, copy and paste the code below:

Don’t be intimidated by the length of the code, it works!

This is the part of the code you can edit as per requirement:

Statement	Description
Const Min As Long = 1	Minimum value
Const Max As Long = 20	Maximum value
Const HowMany As Long = 20	Number of random generations
Const StartRow As Long = 3	Starting row number
Const Col As String = "B"	Starting column

Press the F5 key to run the code. The numbers should appear on the worksheet in the location specified in the code.
Exit the VB

These are the 20 randomly generated numbers without duplicates, using the monster VBA code from above:

Now let’s generate an end to this all. We hope you find our methods easy to follow and easily customizable. Next time you’re wondering how to generate random numbers, you don’t have to download a random number generator; you can come back to our non-random tutorial on random numbers. Work your fingers on some random magic while we work ours to ready your spellbook!

Subscribe and be a part of our 15,000+ member family!

Now subscribe to Excel Trick and get a free copy of our ebook "200+ Excel Shortcuts" (printable format) to catapult your productivity.

Subscribe to ExcelTrick

3 Easy Ways to Generate Random Numbers In Excel

In this tutorial, we will demonstrate how to easily generate random numbers in Excel. In three easy ways, you can use the RAND() function to generate random numbers with different parameters and customize how many numbers you want to generate.

Step-by-step Guide to Generate Random Numbers In Excel

Using rand().

Start by inputting the formula =RAND() in the cell.
Press ENTER and it will immediately show a random number.
Drag down the formula to increase the range that you want.

To ensure that the random numbers in the cells don’t change:

Copy the cells
On the first cell, press RIGHT CLICK
Select PASTE OPTION , choose VALUES

This will delete the original formula and retain the values you copied.

Producing random decimal numbers in the hundreds. In the above example, RAND() always generates decimal numbers from 0 to 1, but you can modify this by changing the formula. To do this, add *100 to the formula to produce random numbers in the hundreds .

Generating random decimal numbers in between the bottom and top values. In the same example, we want to generate decimal numbers in between the bottom and top values. If you have 10000 as the bottom value and 20000 as the top value, you modify the formula by changing it to =10000+10000*RAND() (the top value should be the difference of the two values).

Producing random decimal numbers between 50 and 80. To do this, change the formula to =50+30*RAND() (the difference between 50 and 80 is 30, so this will serve as the top value) and press ENTER.

USING RANDBETWEEN

Input the formula =RANDBETWEEN( in the cell.
To generate random numbers between a set of parameters, input the bottom number as the lowest value and the top number as the highest value (For example, =RANDBETWEEN(10000, 20000) will generate random numbers between 10000 and 20000).
Close the formula with a ) .

USING RANDARRAY

Select the range of cells where you want to display the random numbers.
Type =RANDARRAY in the formula bar
Enter the number of rows and columns you want.
Specify the minimum and maximum value of each number in the array.
Choose between INTEGERS (TRUE) or DECIMALS (FALSE) in the array.
Press ENTER .

Generating random numbers in Excel is an easy and convenient process. With the use of the RAND(), RANDBETWEEN, and RANDARRAY functions, you can quickly generate random numbers with specific parameters and manipulate them as needed. Excel offers a straightforward way to generate and view random numbers, making it an optimal choice for those needing to create random numbers.

How to Generate Random Numbers in Excel: A Step-by-Step Guide

Generating Random Numbers in Excel

Creating random numbers in Excel is super easy! All you need to do is use a couple of built-in functions. With just a few steps, you can generate random numbers for anything you need, whether it’s for simulations, games, or even decision-making. Read on to learn how to do it in a few simple steps.

How to Generate Random Numbers in Excel

In this tutorial, you’ll learn how to generate random numbers in Excel using a step-by-step guide. These steps will help you create random numbers quickly and efficiently.

Step 1: Open Excel

First, open Excel on your computer.

Make sure you have a blank worksheet to work on. This will keep things organized and easy to follow.

Step 2: Select a Cell

Click on a cell where you want the random number to appear.

Choosing a cell in the middle of your worksheet will make it easier to find and modify later.

Step 3: Enter the RAND Function

Type =RAND() in the selected cell and press Enter.

The RAND function generates a random decimal number between 0 and 1. It’s perfect for quick random values.

Step 4: Use the RANDBETWEEN Function

If you need a whole number, type =RANDBETWEEN(bottom,top) in the selected cell and press Enter.

Replace "bottom" and "top" with your desired range. For example, =RANDBETWEEN(1,100) will give you a random number between 1 and 100.

Step 5: Copy the Formula

Click on the cell with the formula, then drag the fill handle (a small square at the bottom-right corner) across other cells.

This will copy the formula, generating random numbers in multiple cells.

Step 6: Fixing the Values

If you need to keep the random numbers from changing, copy the cells and paste them as values.

Right-click the selected cells, choose "Copy," then right-click again and select "Paste Values."

After completing these steps, you’ll have a set of random numbers in your Excel worksheet. You can use these numbers for data analysis, simulations, or any other purpose you need.

Tips for Generating Random Numbers in Excel

Use Named Ranges: Instead of manually entering ranges, you can use named ranges for better readability and easier modifications.
Combine Functions: Don’t hesitate to combine RAND or RANDBETWEEN with other functions for more complex needs, like generating random dates or times.
Macros for Automation: If you frequently need random numbers, consider creating a macro to automate the process.
Statistical Distributions: Use the NORMINV function for normally distributed random numbers if your work requires statistical analysis.
Avoid Recalculation: Set Excel to manual recalculation mode if you don’t want the random numbers to change every time you make an edit.

Frequently Asked Questions

What is the difference between rand and randbetween.

The RAND function generates a random decimal number between 0 and 1, while RANDBETWEEN generates a random whole number between two specified values.

How can I generate random numbers without changing them every time?

Copy the cells with the random numbers and paste them as values using the "Paste Values" option to fix the numbers.

Can I generate random numbers with a specific mean and standard deviation?

Yes, you can use the NORMINV function along with RAND to generate random numbers with a specific mean and standard deviation.

How do I stop Excel from recalculating random numbers?

Go to the Formulas tab, click on Calculation Options, and select Manual. This will stop Excel from recalculating until you manually refresh.

Can I generate random text or dates in Excel?

Absolutely! Combine the RAND or RANDBETWEEN functions with other Excel functions like TEXT or DATE to generate random text or dates.

Summary of Steps

Open Excel.
Select a cell.
Enter the RAND function.
Use the RANDBETWEEN function.
Copy the formula.
Fix the values.

You now know how to generate random numbers in Excel! Whether you’re using the RAND function for decimals or the RANDBETWEEN function for whole numbers, you can easily create the random data you need. With these skills, you can tackle a wide range of tasks, from simple random sampling to complex simulations.

Remember, practice makes perfect, so don’t hesitate to experiment with different functions and combinations. Excel is a powerful tool, and mastering random number generation is just one of many skills that can elevate your data analysis game. Keep exploring and learning, and soon you’ll be an Excel pro!

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time. Read our Privacy Policy

#1 Excel tutorial on the net

Random Numbers

Rand | RandBetween | RandArray | Random Number Generator

Excel has two very useful functions when it comes to generating random numbers . RAND and RANDBETWEEN .

The RAND function generates a random decimal number between 0 and 1.

1. Select cell A1.

2. Type =RAND() and press Enter. The RAND function takes no arguments.

3. To generate a list of random numbers, select cell A1, click on the lower right corner of cell A1 and drag it down.

Note that cell A1 has changed. That is because random numbers change every time a cell on the sheet is calculated.

4. If you want random numbers that remain constant once generated, simply copy the random numbers and paste them as values.

5. Select cell C1 and look at the formula bar. This cell holds a value now and not the RAND function.

RandBetween

The RANDBETWEEN function generates a random whole number between two boundaries.

2. Type =RANDBETWEEN(50,75) and press Enter.

3. If you want to generate random decimal numbers between 50 and 75, modify the RAND function as follows:

If you have Excel 365 or Excel 2021, you can use the magic RANDARRAY function.

1. By default, the RANDARRAY function generates random decimal numbers between 0 and 1. The array below consists of 5 rows and 2 columns.

Note: this dynamic array function, entered into cell A1, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling .

2. The RANDARRAY function below generates an array of decimal numbers, 1 row by 5 columns, between 99 and 100.

3. The RANDARRAY function below generates an array of integers, 10 rows by 1 column, between 20 and 80.

Note: the Boolean TRUE (fifth argument) tells the RANDARRAY function to return an array of integers.

Random Number Generator

To create a random number generator in Excel , simply press F9 or use VBA (our favorite).

2. Type =RANDBETWEEN(0,100) and press Enter.

3. Select cell A1, click on the lower right corner of cell A1 and drag it down to cell A10.

4. Press F9 to generate new random numbers repeatedly.

5. To generate random numbers with no duplicates, combine UNIQUE with RANDARRAY.

Note: press F9 to refresh the random numbers. Sometimes you will see an array that consists of fewer than 10 numbers (when the RANDARRAY function generates duplicates).

You can also use VBA to create a random number generator in Excel. This approach enables you to quickly produce random numbers that remain constant once generated.

1. Select a range of any size! (for example, the range A1:D10).

2. Execute the following code lines:

Note: if you're new to VBA, we're here to help you create and execute a macro .

Statistical Functions

Learn more, it's easy

Negative Numbers to Zero
Percentiles and Quartiles
Box and Whisker Plot
MaxIfs and MinIfs
Weighted Average
Standard Deviation

Download Excel File

random-numbers.xlsx

Next Chapter

Follow excel easy.

Become an Excel Pro

300 Examples

Randomly assign data to groups

Related functions .

To randomly assign rows of data to arbitrary groups, you can use the RANDBETWEEN function with the CHOOSE function . In the example shown, the formula in F5 is:

As the formula is copied down the column, it will return a random group ("A", "B", or "C") at each new row.

Note: this approach will create groups of different sizes. If you need to assign random groups with a fixed size, see this formula .

Generic formula

Explanation .

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below.

The CHOOSE function

The CHOOSE function returns a value from a list of values using an index number. The index number is provided as the first argument, and the values to be selected follow. For example, if we have a list of three colors ("red", "blue", and "green"), we can configure CHOOSE to return each color in turn with the following formulas:

Notice that CHOOSE uses the index number to select the "nth" value from the list of values. The values can be customized in any way you like and the only requirement is that the index number be valid for the number of values provided. Of course, in this example, we don't want to hardcode an index number into CHOOSE, we want a random index number. For this, we can use the RANDBETWEEN function.

The RANDBETWEEN function

The RANDBETWEEN function generates a random number between two integers, provided as the bottom and the top . For example, to generate a random number between 1 and 10, you can use RANDBETWEEN like this:

When Excel's calculation engine updates a worksheet, RANDBETWEEN will generate a random number between 1 and 10.

CHOOSE with RANDBETWEEN

The behavior of RANDBETWEEN will work perfectly for this problem. We have three possible groups ("A","B","C") so we need a random number between 1 and 3, which we can get like this:

The final step is to embed RANDBETWEEN into the CHOOSE function as the index number like this:

This is the formula that appears in cell F5 in the example shown. When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3. This number is delivered directly to the CHOOSE function as the index number, and CHOOSE returns the corresponding color as a final result. You can use this approach whenever you need to assign random text values to each row in a data set. Just be sure to adjust the second argument in RANDBETWEEN, top , to match the number of values provided.

Stopping automatic recalculation

Be aware that RANDBETWEEN is a volatile function and will recalculate whenever there is any change to a workbook, or even when a workbook is opened. To force a recalculation, you can press the F9 key. Once you have a set of random assignments, you may want to stop the formula from returning new results. The classic way to do this is to use Paste Special:

Select all cells that contain the CHOOSE and RANDBETWEEN formula.
Copy to the clipboard with Control + C.
Open the Paste Special window with the shortcut Control + Alt + V.
Select "Values" and click OK:

Select Values in the Paste Special window

After you press OK, all formulas will be replaced with static values.

Dynamic array formula

In the current version of Excel (Excel 2021 or later) you can use a single dynamic array formula to generate all random values at once. One option is to use the RANDARRAY function with CHOOSE like this:

The core idea of this formula is the same as the original formula above. However, instead of RANDBETWEEN, we use RANDARRAY, which can generate an array of random numbers in one step. To figure out how many random numbers to generate, we use the ROWS function on a range corresponding to the first column of the data. This saves us the step of telling RANDARRAY how many rows we need. In this case, ROWS returns 100, because there are 100 rows in the range B5:B104. Simplifying, we now have:

Next, RANDARRAY generates an array of 100 random numbers between 1 and 3. The result is returned to CHOOSE as the index_num argument, and CHOOSE uses the random numbers to return an array that contains 100 random groups. This array lands in cell F5 and spills into the range F5:F104.

INDEX alternative

It is also possible to use the INDEX function instead of CHOOSE in a formula like this:

Like CHOOSE, INDEX retrieves a value based on an index number. INDEX however accepts the values all at once in the first argument, called array . In the formula above, the values "A", "B", and "C" are provided as an array constant to INDEX as the array , and RANDBETWEEN is used as before to generate a random number between 1 and 3. The RANDARRAY version of the formula with INDEX looks like this:

One advantage of INDEX is that the array constant can be replaced with a range on the worksheet. In other words, you can enter group names into a range and provide that range to INDEX. The CHOOSE function will not accept a range of values; it requires that values be provided separately.

Note: the formulas on this page will create completely random groups. One result is that the total number of rows assigned to each group will vary. If you need to assign random groups with a fixed size (i.e. randomly assign people to teams of 6), see the example on this page .

Related formulas

Excel formula: Randomly assign people to groups

Randomly assign people to groups

Excel formula: Random number between two numbers

Random number between two numbers

Excel formula: Random date between two dates

Random date between two dates

Random text values

Excel formula: Random value from list or table

Random value from list or table

Excel formula: Random number from fixed set of options

Random number from fixed set of options

Related functions

RANDBETWEEN Function

The Excel RANDBETWEEN function returns a random integer between two given numbers. RANDBETWEEN recalculates each time a worksheet is opened or changed.

CHOOSE Function

The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.

RANDARRAY Function

The Excel RANDARRAY function generates an array of random numbers between two values. The size or the array is specified by rows and columns arguments. The generated values can be either decimals or whole numbers.

ROWS Function

The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Return to List of Excel Functions

RAND Function Examples – Excel, VBA, & Google Sheets

Editorial Team

Reviewed by

Steve Rynearson

Download the example workbook

This tutorial demonstrates how to use the RAND Function in Excel to calculate a random number.

How to use the RAND Function in Excel:

The RAND function generates numbers (decimals) greater than 0 and lesser than 1.

Note: RANDARRAY is a new function in Excel 2019 onwards and replaces the RAND function. But if you don’t have the latest version or need to send the file out for people who don’t have it yet, you still can use the RAND function for backwards compatibility.

Besides generating random numbers greater than 0 and lesser than 1, RANDARRAY allows you to choose how many rows and columns to place the random numbers, choose the minimum and maximum number instead of just 0 and 1, and even choose if you want whole numbers or decimals.

If you have Excel 2007 onwards, you can use RANDBETWEEN as well. Like RANDARRAY, it allows you to choose the minimum and maximum number instead of just 0 and 1. This example below shows random numbers between 1 and 100.

Generate Whole Numbers within a Range

As mentioned, RANDARRAY and RANDBETWEEN do this easily. With RAND, you need to do it like this:

The formula is basically =RAND()*(Maximum Number – Minimum Number) + Minimum Number. And wrap it with an INT to grab the integer.

Generate Random Dates or/and Time

If you know how dates and time are stored in Excel, the logic is going to be the same as above because they are stored as numbers. Just:

Time is similar, except that it’s decimal instead of a whole number or integer. Hence, remove the INT:

To have them both together, simply add them together:

Retrieve a Random Field

If you simply need to randomize decimals, RAND is perfect by itself. Most of the time though, we want to retrieve a random field. To grab a random employee in B3:B7 for instance, you can use a RAND function to assign to each employee, and INDEX and RANK to grab a random employee:

RAND function will randomize numbers in C3:C7. RANK compares the random number in cell C3 among C3:C7 and ranks the number in descending order. 0.0843531593276469 for instance, ranks as number 3 among C3:C7. INDEX grabs the particular row number 3 from B3:B7 and that’s Sebastian Thompson.

Press the F9 key and the RAND function will recalculate. The value of 0.180669763264754 in cell C3 is now ranked 5 among the numbers in C3:C7 and thus showing the 5 th row in B3:B7 and that’s Cairo Mccall.

Do note that while it’s extremely unlikely for RAND function to generate a duplicate number, it’s still theoretically possible.

Retrieve a Random Field without Duplicates

To ensure there are no duplicates, add a COUNTIF to the formula.

In the above eg, there are two instances of 0.834312895315874. Hence, they both rank the same at 5. With the COUNTIF minus 1 added, the first rank of 0.834312895315874 would be 5 + 1 -1 (5) and the second rank of 0.834312895315874 would be 5 + 2 – 1 (6) and thus pick up different rows.

RAND Function in Google Sheets

The RAND Function works exactly the same in Google Sheets as in Excel:

AI Formula Generator

Try for Free

Excel Help Bot
Formula Generator
Formula Explainer
VBA Code Generator
VBA Code Commenter
Excel Template Generator

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

Automatically Graded Exercises
Learn Excel, Inside Excel!

Free Download

How to Create a Random Number Generator in Excel

Do you need to generate a series of random numbers? Excel makes this simple and easy to do. Here’s how to create a random number generator in Excel.

Do you need to generate a list of random numbers? If so, it’s fairly easy to create a random number generator using Excel.

You can quickly generate a set of random integers between two values of your choice. You can also generate random decimal values, or even create a random list of each value from 1 to 20.

If you’re ready to get random, here’s how to create a random number generator in Excel.

How to Generate Random Numbers in Excel using RANDBETWEEN

If you want to generate random numbers in a particular range, the RANDBETWEEN function is a quick and easy way to do so. This allows you to generate random integers between any two values that you choose.

To generate random numbers in Excel using RANDBETWEEN:

Click in the cell where you want your first random number to appear.

How to Generate Random Numbers in Excel using RAND

You can also achieve the same result by using the RAND function , although this takes a little more effort if you want to generate integers.

To generate random numbers in Excel using RAND:

Click in the cell where you want your first number to appear.

How to Generate Unique Random Numbers in Excel

Due to the way that both RAND and RANDBETWEEN generate numbers, you may find that you get the same value more than once in your list of random numbers. This is to be expected; if the numbers were truly random, repeated values would be likely to occur if you generated enough values.

If you want to randomize a specific set of values, however, then it’s possible to do so. For example, you might want to put the numbers 1 to 20 into a random order.

To generate unique random numbers in Excel:

Click in the cell where you want to generate your first random number.

Press Enter and a value between 1 and 20 will generate.

In the unlikely event that you get a duplicate random decimal in your first row, type = in any empty cell and press Enter . The values will regenerate.

Harness the Power of Excel

Learning how to create a random number generator in Excel is just one of the many ways that you can use Excel for far more than just storing a table of data.

For example, did you know that you can ring an alarm in Excel when certain criteria are met? You can also insert checkboxes in Excel to create useful checklists for work or home. You can also create all manner of charts, from radar charts to box plots .

Your email address will not be published. Required fields are marked *

Save my name and email and send me emails as new comments are made to this post.

Recommended for you

Microsoft Introduces Copilot+ AI Laptops - featured

Ablebits blog
Random data

How to generate random numbers in Excel without duplicates

In this article, we'll discuss a few different formulas to randomize in Excel without repeating numbers. Also, we will show you a universal Random Generator that can produce a list of random numbers, dates, and strings with no repeats.

As you probably know, Microsoft Excel has several functions for generating random numbers such as RAND , RANDBETWEEN and RANDARRAY . However, there is no guarantee that the result of any function will be duplicate free.

This tutorial explains a few formulas for creating a list of unique random numbers. Please pay attention that some formulas only work in the latest version of Excel 365 and 2021 while others can be used in any version of Excel 2019, Excel 2016, Excel 2013 and earlier.

Get a list of unique random numbers with predefined step

Only works in Excel 365 and Excel 2021 that support dynamic arrays.

If you have the latest Excel version, the easiest way for you to get a list of unique random numbers is to combine 3 new dynamic array functions: SORTBY, SEQUENCE and RANDARRAY:

Where n is the number of random values you want to get.

For example, to create a list of 5 random numbers, use 5 for n :

=SORTBY(SEQUENCE(5), RANDARRAY(5))

Enter the formula in the topmost cell, press the Enter key, and the results will spill automatically over the specified number of cells.

Simple formula to produce a list of unique random numbers with predefined step

In the above formula, you only define how many rows to fill. All other arguments are left to their default values, meaning the list will start at 1 and will be incremented by 1. If you'd like a different first number and increment, then set your own values for the 3 rd ( start ) and 4 th ( step ) arguments of the SEQUENCE function.

For instance, to start at 100 and increment by 10, use this formula:

How this formula works:

Working from the inside out, here's what the formula does:

The SEQUENCE function creates an array of sequential numbers based on the specified or default start value and incrementing step size. This sequence goes to the array argument of SORTBY.
The RANDARRAY function creates an array of random numbers of the same size as the sequence (5 rows, 1 column in our case). The min and max value do not really matter, so we can leave these to defaults. This array goes to the by_array argument of SORTBY.
The SORTBY function sorts the sequential numbers generated by SEQUENCE using an array of random numbers produced by RANDARRAY.

Generate a list of random numbers with no duplicates

To generate random numbers in Excel without duplicates, use one of the below generic formulas.

Random integers :

Random decimals :

N is the number of values to generate.
Min is the minimal value.
Max is the maximum value.

For example, to create a list of 5 random integers from 1 to 100 with no repeats, use this formula:

To generate 5 unique random decimal numbers , put FALSE in the last argument of RANDARRAY or omit this argument:

At first sight the formula may look a bit tricky, but upon a closer look its logic is very straightforward:

The RANDARRAY function creates an array of random numbers based on the min and max values that you specify. To determine how many values to generate, you raise the desired number of uniques to the power of 2. Because the resulting array may have no one knows how many duplicates, you need to provide a sufficient array of values for UNIQUE to choose from. In this example, we need only 5 unique random numbers but we instruct RANDARRAY to produce 25 (5^2).
The UNIQUE function removes all duplicates and "feeds" a duplicate-free array to INDEX.
From the array passed by UNIQUE, the INDEX function extracts the first n values as specified by SEQUENCE (5 numbers in our case). Because values are already in random order, it does not really matter which ones survive.

Note. On very large arrays, this formula may be a bit slow. For example, to get a list of 1,000 unique numbers as the final result, RANDARRAY would have to generate of array of 1,000,000 random numbers (1000^2) internally. In such situations, instead of raising to power, you can multiply n by, say, 10 or 20. Just keep in mind please that the smaller array is passed to the UNIQUE function (small relative to the desired number of unique random values), the bigger the chance that not all cells in the spill range will be filled with the results.

Create a range of non-repeating random numbers in Excel

To generate a range of random numbers with no repeats, you can use this formula:

n is the number of cells to fill. To avoid manual calculations, you can supply it as (no. of rows * no. of columns). For example, to fill 10 rows and 5 columns, use 50^2 or (10*5)^2.
Rows is the number of rows to fill.
Columns is the number of columns to fill.
Min is the lowest value.
Max is the highest value.

As you may notice, the formula is basically the same as in the previous example. The only difference is the SEQUENCE function, which in this case defines both the number of rows and columns.

For instance, to fill a range of 10 rows and 3 columns with unique random numbers from 1 to 100, use this formula:

=INDEX(UNIQUE(RANDARRAY(30^2, 1, 1, 100)), SEQUENCE(10, 3))

If you need whole numbers, then set the last argument of RANDARRAY to TRUE:

How to generate unique random numbers in Excel 2019, 2016 and earlier

As no version other than Excel 365 and 2021 supports dynamic arrays, none of the above solutions work in earlier versions of Excel. However, this does not mean there is no solution at all, you'll just have to perform a few more steps:

The RAND function to generate random decimals between 0 and 1, or
The RANDBETWEEN function to produce random integers in the range that you specify.

Be sure to generate more values than you actually need because some will be duplicates and you'll delete them later.

For this example, we are creating a list of 10 random integers between 1 and 20 by using the below formula:

=RANDBETWEEN(1,20)

To enter the formula in multiple cells in one go, select all the cells (A2:A15 in our example), type the formula in the formula bar and press Ctrl + Enter . Or you can enter the formula in the first cell as usual, and then drag it down to as many cells as needed.

Done! All duplicates are gone, and you can now delete the excess numbers.

Tip. Instead of Excel's built-in tool, you can use our advanced Duplicate Remover for Excel .

How to stop random numbers from changing

All of the randomizing functions in Excel including RAND, RANDBETWEEN and RANDARRAY are volatile, meaning they recalculate every time the spreadsheet is changed. As the result, new random values are produced with every change. To prevent generating new numbers automatically, use the Paste Special > Values feature to replace formulas with static values. Here's how:

Select all the cells with your random formula and press Ctrl + C to copy them.
Right click the selected range and click Paste Special > Values . Alternatively, you can press Shift + F10 and then V , which is the shortcut for this option.

Random number generator for Excel with no repeats

The users of our Ultimate Suite do not really need any of the above solutions because they already have a universal Random Generator in their Excel. This tool can easily produce a list of non-repeating integers, decimals numbers, dates, and unique passwords. Here's how:

On the Ablebits Tools tab, click Randomize > Random Generator .
Select the range to fill with random numbers.
Choose the desired value type: integer, real number, date, Boolean, custom list, or string (ideal for generating strong unique passwords!).
Set up the From and To values.
Select the Unique values check box.
Click Generate .

Random number generator for Excel with no repeats

That's how to randomize numbers in Excel without duplicates. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

36 comments

I have modified the formula to incorporate the new TAKE function and also the RANDARRAY, to have the INDEX function pick 5 randomly and unique rows, instead of just the first 5 permitted by the use of SEQUENCE.

=INDEX(UNIQUE(RANDARRAY(5^2,1,1,100)),TAKE(UNIQUE(RANDARRAY(25,1,1,25,TRUE)),5))

I have tried to apply this formula =INDEX(UNIQUE(RANDARRAY(120^2, 1, 1, 50, TRUE)), SEQUENCE(15,8)) to get 15 rows * 8 columns of numbers 1 to 50 with no repeats in the rows. The formula works for like 5 or 6 rows correctly and then the #REF! error populates the other cells down.

Also, would it be possible to wrap this formula with a sortby function such that instead of the result reading as (eg. 38,15,16,4,12,46,43,48), I get the same values but sorted like this (4,12,15,16,38,43,46,48)

Please and Thanks,

Hi! You want to write 50 numbers in 120 cells without repeating them? Sorry, that is not possible. Increase the quantity of numbers. For example,

=INDEX(UNIQUE(RANDARRAY(120^2, 1, 1, 500, TRUE)), SEQUENCE(15,8))

Wow, that was quick. Thanks Alexander.

My apologies. Here is a better example of what happens S.No. 1 - 11,18,24,27,29,40,42,50 2 - 1,6,8,19,23,26,46,47 3 - 3,9,13,14,25,28,34,39 4 - 4,5,10,20,32,35,37,41 5 - 7,12,16,30,33,36,43,45 6 - 2,15,17,22,38,44,48,49 7 - 21,31,#REF!,#REF!,#REF!,#REF!,#REF!,#REF! 8 - #REF!,#REF!,#REF!,#REF!,#REF!,#REF!,#REF!,#REF! 9 - #REF!,#REF!,#REF!,#REF!,#REF!,#REF!,#REF!,#REF!

Did you use the formula I gave you?

i am in need of values from 1 to 50. replacing 50 with 500 does remove the #Ref! errors but does not help as the numbers are above 50.

Then find a way to place 50 numbers in 120 cells without repeating them. I think this will be very much appreciated.

That's perfect! Thank you very much, Svetlana!

I 'm using VBA with randombetween function and I think it is the best way to generate unique random numbers (as example range of random numbers between 100 to 1000) . The VBA code check the current generated number with all previous numbers if exist then it must be regenerated again and so on.

I have a random generator file for choosing swabbing sites. Once a year we need to go through all the sites, and we swab weekly. How can I format the file so that it does not ever duplicate a swab site for the year? Is this possible? We are randomly picking 7 sites weekly, but I don't want it to duplicate till all the sites have been swabbed, and then it could start over.

The equation I'm currently using is: =INDEX(UNIQUE(RANDARRAY(7^2,1,1,312,TRUE)),SEQUENCE(7))

I could randomize all 312 sites, just wondering if there is a better way to accomplish the task. Multiple people use the file, so I want something user friendly for everyone using it.

Is this possible to do in Excel?

Hello! Random values can only be generated over a continuous range of numbers. You can check the site check box in a separate column. Then you create a new list of unverified sites. Change the number 312 in the formula to a smaller one.

Hi There- I Loved these formulas!! It was super helpful, though I am trying to see if there is a related formula (or pair of them) for allowing 'less than e.g. 10% repeat rates. For example, if we started with 1500 product testers, and we had 100 testers per product, how could we generate groups of 100 per product such that no more than 10% of the 100 were repeats (i.e. groups of 100 were 90% unique). Thanks in advance for your insight! Best Regards, JF

Hello! Unfortunately, Excel formulas cannot control how many duplicates will appear in the list.

Hello! I am using this formula but need two columns of random numbers. 1-9 are the numbers. If first column number is 1-5 then second column needs to be greater than 5 and vica versa, if first column is >5 then second column 5 =<. I was thinking I’d have to imbed another formula into a if then? I’m pretty rusty with excel.

Hello! Sorry, I do not fully understand the task. What formula are you using? Write an example of the result you want to get.

Thanks for the reply! So I’m assigning daily schedules to 9 employees for Morning and afternoon. 9 schedules, 9 employees. I used =sortby(sequence(9),randarray(9)) to assign the AM schedules. For PM, anyone with a schedule 1-5 in AM should be assigned schedules 6-9 and if 6-9 in am, 1-5 in afternoon. I did a combination of IF and randbetween but of course get duplicates in afternoon

So far I need this to not have duplicates: =if(b12<=5,randbetween(6,9),randbetween1,4))

Hi! Write an example of what values you want to see in column B and column C. I think that for 5 people (1-5) it is impossible to assign 4 numbers (6-9) without duplicates! Or have I misunderstood something?

B. C 5. 7 3. 6 7. 4 4. 5 8. 1 6. 2 2. 3 1. 9

Hopefully formatting will hold when I post this.

Correction. That 9 in C column can be an 8. This example has 8 employees and 8 schedules. Different days have different numbers of employees available for schedules.

Hi! As far as I can see from your last comment, your task is now different from the original one. Your example result is completely different from what you wrote earlier. In these numbers, I do not see any pattern. I don’t think I can help with such a task.

I am also having a problem with the formula:

=INDEX(UNIQUE(RANDARRAY(4, 10, 1, 10, TRUE)), SEQUENCE(4), {1,2,3,4,5,6,7,8,9,10}).

I am basically trying to produce a Sudoku table much like the example above with the tennis players.

I want to have 4 inspectors test 10 parts with them testing at the same time (if part 1 is being tested by inspector 1, it cannot be available to the other 3 inspectors during that run and inspector 1 should only be shown part 1 one time).

The array should only produce 1 number across a row and column.

Hi! The information you provided is not enough to understand your case and give you any advice, sorry. Describe in detail what problem you have, and I will try to help you.

thanks for your blog. I have volunteered to build a tennis schedule for my club, this requires random teams of 4 for each of 4 time slot (total 16 per week). Unfortunately there are 20 players, so 4 'sit out' each week. E.G. week 1, players 1-16 play, (these are to be randomized) and players 17-20 'sit out'. In week 2 perhaps players 5-20 play and 1-4 'sit out'. Each number has a name associated with it. I tried this formula across my players both in and out =INDEX(UNIQUE(RANDARRAY(30^2,1,1,20,TRUE)),SEQUENCE(1,20)) but the 'out' players were treated like any other group of 4 and so they were not fairly randomized (they should only sit out 2 times in an 8 week rotation. I was wondering if there is a way to input a dynamic range so that each week it can look at my non randomized list of players who are out for a week and generate the random numbers from the residual list of 1-20 for my list of players :)

Any update to Richard's challenge up above? I'm in the same boat having volunteered to build a Pickleball schedule for my club and facing the same challenge

Hi! Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

Hello, thanks for this interesting topic. I wondered to know how to generate unrepeated random number starting always by the same number, like 12 for exemple?

Hi, The formula below will do the trick for you:

=--("12"&RANDBETWEEN(100,200))

I hope my advice will help you solve your task.

PREZADA EXISTE UMA FORMULA EM EXCEL PARA GERAR NUMEROS ALEOTÓRIOS EXCLUINDO ALGUNS. EX GERA RANDOM ENTRE 1 E 30 EXCLUINDO OS NUMEROS 11,24 E 29

Hi, I have a query regarding the following function:

=INDEX(UNIQUE(RANDARRAY(20, 3, 1, 100, TRUE)), SEQUENCE(10), {1,2,3})

You have provided this formula under the heading "Create a range of non-repeating random numbers in Excel". But the example array you have shown contains repeating numbers. Eg: 65 appears twice, 25 apears twice, 61 appears twice and so on. Your array does not appear to contain 30 unique random numbers, because several of them are repeated.

I am trying to create a 5 x 5 array, with randomly generated numbers between 1 and 50, without any duplicates. So the result I need is 25 unique numbers in the range 1 to 50, in a 5 x 5 table. This is my formula based on your example:

=INDEX(UNIQUE(RANDARRAY(50, 5, 1, 50, TRUE)), SEQUENCE(5), {1,2,3,4,5})

But I am yet to get a 5 x 5 array with 25 unique numbers. There are always several numbers that are repeated in the array, sometimes in duplicate, sometimes even in triplicate. I thought at first, maybe my range (1 to 50) is not big enough? So I extended it from 1 to 100, but still got repeating numbers.

In any case, I thought the UNIQUE function would weed out any duplicated numbers. So really, it shouldn't matter whether I have 1 to 50 or even 1 to 500 as my range, isn't the job of "UNIQUE" to stop two numbers that are the same from ending up in the array?

My question is: how can I get my 5 x 5 array to have 25 unique numbers with values between 1 and 50 without any duplication of numbers?

I am using the latest 365 version of Excel.

Thank you for any assistance you can offer.

The formula is incorrect, sorry for misleading you. Trying it figure it out now (and how I could have overlooked duplicate numbers in the screen). We will post an update asap. Again, my apologies for the inconvenience.

We have fixed the formula and updated the example (thanks to Alex , one of our best Excel gurus, for this solution!).

During my tests, the improved formula worked perfect with no repeats. We will appreciate if you could test it on your side too and let us know about your results. Thank you in advance!

In my question/comment, I meant to say that I'm interested in generating unique random numbers across rather than down.

Dear Dr. Rapoport,

If you use Excel 365, then you can find a solution in How to create a range of unique random numbers . That formula produces no duplicates in either rows or columns.

Generate random numbers without Ablebits works down columns. It does not seem to work across rows, because if I produce an array using any of these methods, there are often duplicates in a row. Is there a way to fix that? Thanks.

Hello! There are a lot of formulas in the article. Clarify which of them are you talking about? Try this formula

=INDEX(UNIQUE(RANDARRAY(2, 10, 1, 100)), SEQUENCE(1), {1,2,3,4,5,6,7,8,9,10})

How-To Geek

How to randomize a list in microsoft excel.

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

It's Time to Let Us Resell Digital Games

How to set up a kodi media center on linux, 10 ps4 games you should play again on ps5, quick links, how excel's list randomization works, use rand to randomize list items in excel.

If you want to rearrange items in a list in random order, you can use Microsoft Excel's

function. The function generates random numbers , which allow you to then randomize your list items. Here's how to do that in your spreadsheet.

Related: How Computers Generate Random Numbers

In Microsoft Excel, you don't have a feature to randomize your list items in a click. Instead, you have to rely on the app's

function that generates a series of random numbers . You then use these random numbers as the base for randomizing your list.

Related: How to Generate Random Numbers in Microsoft Excel

Basically, you add the

function's column next to your list, so that you have a random number next to each item on your list. Then, you sort these random numbers in ascending or descending order, which causes your list items to rearrange, too.

Once your items are randomized, you may delete the

function column as you don't need it anymore.

To start the randomization process, first, open your spreadsheet with Microsoft Excel.

In your spreadsheet, select the row next to the first item on your list.

Click the row next to the first item on the list.

In the selected row, enter the following function and press Enter:

Enter the RAND function and press Enter.

In the cell where you typed the function, you will see a random number. To automatically fill random numbers next to each item on your list, then in this cell's bottom-right corner, double-click the small dot.

Select the small dot at the bottom-right corner of the cell.

You now have a random number next to each item on your list.

To begin randomizing your list items, in Excel's ribbon at the top , click the "Home" tab.

In the "Home" tab, click Sort & Filter > Sort Smallest to Largest. You may choose "Sort Largest to Smallest" as well if you want. It doesn't matter what option you choose as your list items are going to be randomized in either case.

Each time you want to rearrange your items in random order, select the "Sort Smallest to Largest" or "Sort Largest to Smallest" option.

Choose Sort & Filter > Sort Smallest to Largest.

Your random numbers will be sorted , and when this happens, Excel will put your list items in random order, too.

You now have your list in completely random order. Now that you don't need the generated random numbers anymore, get rid of them by right-clicking the column's letter at the top and choosing "Clear Contents."

And that's all there is to applying Excel's magic to come up with random list order.

If you use Google Sheets, you can generate random numbers in Sheets , too.

Related: How to Generate Random Numbers in Google Sheets

Microsoft Office
Microsoft Excel

How to Randomly Select from a List in Excel (5 Methods)

Our sample dataset represents participants in a lottery. We’ll randomly select a winner.

Method 1 – Using the INDEX and RANDBETWEEN Functions to Select from a List Randomly

Case 1.1 – inserting the list range manually.

In Cell D5 , insert the following formula:
Hit Enter, and you will get a random value like the image below.

Using INDEX and RANDBETWEEN Functions to Select from a List Randomly

Formula Breakdown:

RANDBETWEEN(1,8)

It will return a random number among the numbers from 1 to 8.

INDEX(B5:B12,RANDBETWEEN(1,8))

The INDEX function returns the corresponding value from the list according to the number.

Whenever we press Calculate Now from the Formulas tab, it will return a new random value.

Case 1.2 – Inserting the List Range Using the ROWS Function

Use the following formula in Cell D5:

ROWS(B5:B12)

It will return the total number of rows that used the list and actually that expresses the maximum number of the list.

RANDBETWEEN(1,ROWS(B5:B12))

The RANDBETWEEN function will return a random number within the range.

INDEX(B5:B12,RANDBETWEEN(1,ROWS(B5:B12)))

The INDEX function will extract the value according to the number.

Case 1.3 – Inserting the List Range Using the COUNTA Function

This formula works as the previous formula , just using the COUNTA function instead of the ROWS function to return the highest range as the number of rows and cells in a column within the rows is the same eventually.

Method 2 – Using Excel CHOOSE and RANDBETWEEN Functions to Select from a List Randomly

Insert the following formula in Cell D5:
Hit the Enter button.

This will return a random number from 1 to 8.

CHOOSE(RANDBETWEEN(1,8),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10,$B$11,$B$12)

The CHOOSE function will return the value from the range of the list according to the random number.

Method 3 – Combining INDEX, RAND, and RANK Functions to Randomly Select Multiple Values

Insert a helper column next to the list column.
Insert the following formula to generate some random values in the new column:
Press the Enter button.

Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

Drag down the Fill Handle icon to copy the formula for the other cells.

We get random values for each name.

Use the following formula in Cell E5:

RANK(C5,$C$5:$C$12)

The RANK function will create random rank numbers for every value of the range C5:C12 .

INDEX($B$5:$B$12,RANK(C5,$C$5:$C$12))

The INDEX function will extract the value according to the number.

Use the Fill Handle tool to copy the formula and get the other two random values.

We got three random values.

Method 4 – Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

Use Method 3 to fill column C.
Insert a new helper columns.
Hit Enter .

Use the Fill Handle tool to copy the formula.

This gives each person a random rank.

Insert the following formula in Cell G5:
Hit the Enter button to get the first random value.

MATCH(F5,$D$5:$D$12,0)

The MATCH function will return the relative position from the array according to the specific value.

INDEX($B$5:$D$12,MATCH(F5,$D$5:$D$12,0),1)

The INDEX function will extract the corresponding value according to the relative position.

Use the Fill Handle tool to get the other two random values.

We got three different random names. Each time you will recalculate, you will get new names.

Method 5 – Using XLOOKUP to Randomly Select Multiple Values Without Duplicates in Excel

Use Method 3 and 4 to fill in two helper columns C and D.
Use the formula given below in Cell G5:
Press the Enter button to get the array.

Using XLOOKUP Function to Randomly Select Multiple Values Without Duplicates in Excel

Here are our random names as an array output.

Download the Practice Workbook

<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel

What is ExcelDemy?

Tags: Random Selection in Excel

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

random number assignment excel

IMAGES

COMMENTS

5 Ways to Generate Random Numbers in Excel

Generate Random Numbers with the RAND function

Syntax for the RAND Function

Generate Random Numbers Between Any Two Numbers

Generate Random Integer Numbers Between Any Two Numbers

Generate Random Numbers using the RANDBETWEEN Function

Syntax for the RANDBETWEEN Function

Example with the RANDBETWEEN Function

Generate Random Numbers with the RANDARRAY Function

Syntax for the RANDARRAY Function

Example with the RANDARRAY Function

Generate Random Numbers with the Analysis Tools Add-In

Install the Analysis Toolpak

Generate Random Numbers with the Analysis Toolpak

Generate Random Numbers with VBA

Generate Random Numbers without Duplicates or Repeats

Solution with RANK.EQ and COUNTIF Functions

Solution with VBA

Solution with Dynamic Arrays

About the Author

John MacDougall

Related Posts

7 Ways To Recover Unsaved Microsoft Excel Files

8 Ways To Collapse Rows in Excel

4 Ways To Calculate Days Between Dates in Microsoft Excel

Get the Latest Microsoft Excel Tips

How to Generate Random Numbers in Excel (Without Duplicates)

Random Number Generating Functions in Excel

RANDBETWEEN Formula

RAND Formula

Using RAND and RANK to get Random Numbers in Excel Without Duplicates

Using RAND, LARGE and MATCH to get Random Numbers in Excel Without Duplicates

Leave a Comment Cancel reply

RAND function

Description

Need more help?

Want more options?

Was this information helpful?

How to Generate Random Numbers in Excel

Generate Random Numbers in Excel

Generate Random Numbers using RANDBETWEEN function in Excel

Generate Unique Random Numbers using RAND and RANK function in Excel

3 thoughts on “How to Generate Random Numbers in Excel”

Leave a Comment Cancel reply

How to Generate Random Numbers in Excel: RAND & RANDBETWEEN

Random numbers with RAND

That’s it – Now what?

Other resources

How do I generate multiple random numbers in Excel?

How do I generate a random number between 1 to 100 in Excel?

How to Generate Random Numbers in Excel

Understanding Randomness in Excel

Using the RAND Function

Step 1: Select a Cell

Step 2: Enter the RAND Function

Step 3: Copy and Paste the RAND Function

Generating Whole Numbers using the INT Function

Step 2: Enter the INT Function

Step 3: Copy and Paste the Formula

Using the RANDBETWEEN Function

Step 2: Enter the RANDBETWEEN Function

Generating Random Dates and Times

Step 2: Enter the RAND Function for Dates and Times

Can I generate random numbers without decimals?

What’s the difference between the RAND and RANDBETWEEN functions?

Can I generate random dates and times in Excel?

Can I generate a list of unique random numbers in Excel?

Can I generate random numbers based on certain criteria?

Featured Companies

Resultris Marketing

Other Categories

How to Use Structured References in Excel

How to Remove Drop-down in Excel

How to Group Worksheets in Excel

How to Recover Excel File Not Saved

How to Remove Subtotals in Excel

How to Unlock a Cell in Excel

How to Calculate Hours Worked in Excel

How to Calculate Covariance in Excel

How to Add Dates in Excel