In this article, well 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.
Working with numbers is one of the main uses of Excel. Whether it’s analyzing data building financial models or running simulations, randomizing numbers in Excel is an important skill to master. In this comprehensive guide, I’ll show you several methods to generate randomized numbers in Excel.
Why Randomize Numbers in Excel?
There are many reasons you may want to generate randomized numbers in Excel
-
Running Monte Carlo simulations or other statistical analyses where you need random data sets.
-
Building financial models and want to run scenarios with different randomized inputs.
-
Testing business cases with randomized figures to stress test models.
-
Selecting random samples from a data set.
-
Even just selecting random winners for a competition or lottery!
The key point is that generating random numbers allows you to mimic randomness and probabilities within a spreadsheet model. This guide will equip you with the skills to do just that in Excel.
Generate Random Numbers with RAND
The simplest way to generate a random number in Excel is using the RAND() function.
For example:
=RAND()This will produce a random decimal number between 0 and 1. The number will be different each time the cell is calculated.
You can also generate a random number between 0 and a set maximum by multiplying the result:
=RAND()*100 This will generate a random number between 0 and 100.
To get a random integer, you can combine RAND() with the ROUND() or INT() functions:
=ROUND(RAND()*100,0) =INT(RAND()*100)Both will generate random whole numbers between 0 and 100.
To generate numbers between a minimum and maximum value, you can add to the random number:
=INT(RAND()*90)+10This will generate random integers between 10 and 100.
Generate Random Numbers with RANDBETWEEN
An even easier way is to use Excel’s RANDBETWEEN() function. This allows you to specify a minimum and maximum value directly.
The syntax is:
=RANDBETWEEN(min,max)For example:
=RANDBETWEEN(1,6)This will generate a random integer between 1 and 6.
You can also use negative numbers:
=RANDBETWEEN(-5,5)This will generate random integers between -5 and 5.
The key benefit of RANDBETWEEN is that your minimum and maximum range is defined in the formula. There are no additional calculations required.
Generate Random Numbers in a Range
Often you don’t just want a single random number, but many numbers across a range of cells.
There are several ways to do this:
-
Copy a formula down a column using the fill handle. Each cell will have its own random number.
-
Use Ctrl + D to quickly fill down the column after entering the initial formula.
-
Use the RANDARRAY() function (Excel 365 only) to generate a full array of values.
For example, this formula will create a 2 row by 3 column array of random numbers between 1 and 5:
=RANDARRAY(2,3,1,5)The RANDARRAY() function is very convenient for generating lots of randomized numbers at once.
Generate Non-Repeating Random Numbers
A common requirement is to generate random numbers with no repeats. For example, you may want to select 5 random unique numbers from 1 to 20.
There are a few ways to handle this:
-
Use the Analysis ToolPak add-in random number generator which has an option to generate non-repeating random numbers.
-
With Excel 365, use
RANDARRAY()to generate an array bigger than you need, then take a subset usingINDEX()to get unique random numbers. -
Use a helper column with
COUNTIF()formulas to check for duplicate values as you generate random numbers.
The Analysis ToolPak method is easiest if you have access to it. Otherwise, the RANDARRAY() and INDEX() approach works well to filter a larger randomized set down to unique values.
Set a Random Number Seed
By default, Excel generates completely random values each time a sheet is calculated.
You may want to generate repeatable random numbers, for example, to test different scenarios in a model.
To do this, you can set the seed value that the random number generator uses:
-
In
RAND(), useRANDOMIZEto set the seed first. -
In the Analysis ToolPak, you can specify the Random Seed value.
-
In VBA, set the Rnd seed before calling Rnd for random numbers.
Using a seed will cause Excel to generate the same repeatable sequence of random numbers each time.
Generate Random Numbers in VBA
Excel’s VBA programming language also includes functions to generate random numbers:
-
Use
Rndto get a random number between 0 and 1. -
Set the seed first with
Randomizeto generate repeatable results. -
Use VBA functions like
IntandRoundto get random integers within a range.
VBA gives you complete control over the random number generation if you need to build it into a macro.
Here is an example to generate 10 random whole numbers between 1 and 6:
Sub RandomNumbers() Dim i As Integer For i = 1 to 10 Cells(i,1).Value = Int((6 - 1 + 1) * Rnd + 1) Next iEnd SubAvoiding Common Random Number Pitfalls
When working with random numbers in Excel, some common pitfalls to be aware of:
-
Be careful with worksheet dependencies – a random number will change on every calcuation.
-
Watch for errors like #NUM! if your random range is invalid.
-
Test carefully when using random numbers in critical models to ensure validity.
-
Remember results will vary each time unless you set a fixed seed.
With some caution, Excel’s tools for generating random numbers are very powerful. Just be wary of where and how you use them.
Next Steps for Randomizing Numbers
Here are some suggestions for ways to continue learning about random number generation with Excel:
-
Try using random numbers to simulate dice rolls, card dealing, or roulette spins.
-
Build a Monte Carlo simulation of stock price movements using Excel.
-
Use randomized data sets to test statistical analyses.
-
Construct a financial forecast model using randomized inputs and distributions.
-
Create a probability calculator for events like the chances of winning the lottery.
Learning by doing is a great way to get comfortable with the various methods available for generating random numbers in Excel.
Whether you need to run complex simulations or simply pull a winner out of a hat, generating random numbers is key functionality in Excel.
Using functions like RAND(), RANDBETWEEN() and RANDARRAY() give you the tools to quickly create randomized datasets.
Combined with features like setting a random seed and generating non-repeating values, you can mimic randomness in a robust and repeatable way.
So next time you need to introduce a bit of unpredictability into your spreadsheet, make sure to leverage Excel’s extensive capabilities for generating random numbers. With some practice, you’ll be randomly generating data like a Monte Carlo pro!
Generate a list of random numbers with no duplicates
Only works in Excel 365 and Excel 2021 that support dynamic arrays.
To generate random numbers in Excel without duplicates, use one of the below generic formulas.
Random integers: INDEX(UNIQUE(RANDARRAY(n^2, 1, min, max, TRUE)), SEQUENCE(n))
Random decimals: INDEX(UNIQUE(RANDARRAY(n^2, 1, min, max, FALSE)), SEQUENCE(n))
Where:
- 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:
=INDEX(UNIQUE(RANDARRAY(5^2, 1, 1, 100, TRUE)), SEQUENCE(5)) 
To generate 5 unique random decimal numbers, put FALSE in the last argument of RANDARRAY or omit this argument:
=INDEX(UNIQUE(RANDARRAY(5^2, 1, 1, 100)), SEQUENCE(5)) 
How this formula works:
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.
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: SORTBY(SEQUENCE(n), RANDARRAY(n))
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:
Enter the formula in the topmost cell, press the Enter key, and the results will spill automatically over the specified number of cells.
As you can see in the screenshot below, this formula actually sorts numbers from 1 to 5 in random order. If you need a classic random number generator with no repeats, then please check out other examples that follow below. 
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 youd like a different first number and increment, then set your own values for the 3rd (start) and 4th (step) arguments of the SEQUENCE function.
For instance, to start at 100 and increment by 10, use this formula:
=SORTBY(SEQUENCE(5, , 100, 10), RANDARRAY(5)) 
How this formula works:
Working from the inside out, heres 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.
Please keep in mind that this simple formula creates a list of non repeating random numbers with a predefined step. To bypass this limitation, use an advanced version of the formula described below.
How to Randomize a List In Excel
FAQ
How do I get random numbers from 1 to 100 in Excel?
How to generate random numbers without repetition in Excel?
How to generate a random number in Excel?
Using Excel’s RAND formula, you can produce a random number between 0 and 1. This formula generates highly random and distinctive decimals. This means that there is a low chance of repeating numbers if you generate a set of randomized numbers. To perform this task, use these steps:
How do I get a random number that doesn’t change?
To get a single random number that doesn’t change, enter RANDBETWEEN in the formula bar, press F9 to convert the formula to a static result, and press Enter to enter the value in the cell. Note: in
How to sort numbers in random order in Excel?
Where n is the number of random values you want to get. Enter the formula in the topmost cell, press the Enter key, and the results will spill automatically over the specified number of cells. As you can see in the screenshot below, this formula actually sorts numbers from 1 to 5 in random order.
