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 while others can be used in any version of Excel 2019, Excel 2016, Excel 2013 and earlier.
Only works in the latest versions of Excel 365 that support dynamic arrays.
If you have a Microsoft 365 subscription, 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.
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 you'd 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, here's what the formula does:
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.
Only works in the latest versions of Excel 365 that support dynamic arrays.
To generate random numbers in Excel without duplicates, use one of the below generic formulas.
Random integers:
Random decimals:
Where:
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:
Only works in the latest versions of Excel 365 that support dynamic arrays.
To generate a range of random numbers with no repeats, you can use this formula:
Where:
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))
And it will produce an array of random decimals without repeating numbers:
If you need whole numbers, then set the last argument of RANDARRAY to TRUE:
=INDEX(UNIQUE(RANDARRAY(30^2, 1, 1, 100, TRUE)), SEQUENCE(10,3))
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:
For the detailed steps, please see How to change formulas to values in Excel.
As no version other than Excel 365 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:
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.
Anyway, the result will look something like this:
As you may notice, we've entered the formula in 14 cells, though eventually we only need 10 unique random numbers.
To make sure you've done it right, select any number and look at the formula bar. It should now display a value, not a formula:
Done! All duplicates are gone, and you can now delete the excess numbers.
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:
That's it! The selected range gets filled with non-repeating random numbers at once:
If you are curious to try this tool and explore other fascinating features included with our Ultimate Suite, you are welcome to download a trial version. If you like the tools and decide to get a license, don't miss this special opportunity:
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!
Generate unique random numbers in Excel (.xlsx file)
8 responses to "How to generate random numbers in Excel without duplicates"
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})
Thank You.
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.
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.
Hi Shani,
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.
Shani,
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!
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