random number generator excel between range no duplicates

• Awards Season
• Big Stories
• Pop Culture
• Video Games
• Celebrities

The Importance of Randomness in API Key Generation Algorithms

In today’s digital age, Application Programming Interfaces (APIs) have become an integral part of software development. APIs allow different software systems to communicate and interact with each other seamlessly. One crucial aspect of API usage is the generation and management of API keys. These keys serve as credentials that authenticate and authorize access to APIs. In this article, we will explore the importance of randomness in API key generation algorithms.

Introduction to API Keys

API keys are unique alphanumeric codes that identify a user or application accessing an API. They act as a secret token that establishes trust between the API provider and the consumer. Without proper authentication using an API key, unauthorized access can lead to security breaches, data leaks, and misuse of resources.

The Role of Randomness

Randomness plays a crucial role in ensuring the security and uniqueness of API keys. A random key is one that cannot be easily guessed or predicted by potential attackers. When generating an API key, it is essential to use a proven random number generator algorithm that produces unpredictable results.

Security Implications

Using a non-random or predictable algorithm for generating API keys can have severe security implications. Attackers can exploit patterns or predictability in key generation algorithms to gain unauthorized access to APIs or perform malicious actions on behalf of legitimate users.

Randomness adds an extra layer of protection by making it extremely difficult for attackers to guess or reverse-engineer valid API keys. It helps prevent brute-force attacks where attackers systematically try out different combinations until they find a valid key.

Generating Strong Random Keys

To generate strong random keys for your APIs, consider using cryptographic libraries or built-in functions provided by programming languages specifically designed for secure random number generation.

Avoid using simple patterns such as sequential numbers, dates, or common words as they are easy to guess or predict by potential attackers.

Additionally, ensure that your key generation algorithm follows best practices and industry standards for randomness, such as using a secure entropy source and applying cryptographic hashing functions to further enhance the security of the generated keys.

In conclusion, randomness is a crucial factor in API key generation algorithms. Randomness ensures the uniqueness and security of API keys, making it difficult for attackers to guess or predict valid keys. By using proven random number generators and following best practices, developers can enhance the security of their APIs and protect sensitive data from unauthorized access.

This text was generated using a large language model, and select text has been reviewed and moderated for purposes such as readability.

MORE FROM ASK.COM

• CELL REFERENCE

Post your problem and you'll get expert help in seconds

• All articles

How to Use the RANDBETWEEN Function with No Duplicates

Excel allows us to create to generate random numbers list without duplicates using the RANDBETWEEN, RANK.EQ and COUNTIF functions. This step by step tutorial will assist all levels of Excel users to learn how to use the RANDBETWEEN function with no duplicates.

Syntax of the RANDBETWEEN Formula

=RANDBETWEEN(bottom, top)

The parameters of the RANDBETWEEN function are:

• bottom – a value from which we want to get a random value
• top – a value to which we want to get a random value

Syntax of the RANK.EQ Formula

The generic formula for the RANK.EQ function is:

=RANK.EQ(number, ref, [order])

The parameters of the RANK.EQ function are:

• number – a number for which we want to find the rank in the array
• ref – the array where we want to rank the number
• [order] – a type of ranking data in the ascending or the descending order (0 or omitted value).

Syntax of the COUNTIF Formula

The generic formula for the COUNTIF function is:

=COUNTIF(range, criteria)

The parameters of the COUNTIF function are:

• range – the range from which we want to count the non-blank cells
• criteria – the criteria for counting the cells in the range

Generate Random Number List With No Duplicates in Excel

In column B, we will first generate random numbers from 10 and 30 using the RANDBETWEEN function. As the function can generate duplicate numbers, in column C, we will generate a new list of numbers without duplicates.

The formula in column B looks like:

=RANDBETWEEN(10, 30)

The bottom parameter of the function is 10, while the top parameter is 30.

To apply the RANDBETWEEN formula, we need to follow these steps:

• Select cell B3 and click on it
• Insert the formula: =RANDBETWEEN(10,30)
• Press enter
• Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Now we can use the combination of RANK.EQ and COUNTIF functions to create a list without duplicates.

The formula in the cell C3 looks like:

=9 + RANK.EQ(B3, $B$3:$B$23) + COUNTIF($B$3:B3, B3) - 1

At the beginning of the formula, we must add 9 to the result, as the range begins from 10. The parameter range in the COUNTIF function is the cell range $B$3:B3 while the criteria is the cell B3. The number parameter of the RANK.EQ function is the cell B3, while the parameter ref is the range $B$3:$B$23. An order is omitted because we want to rank in descending order.

To apply the formula, we need to follow these steps:

• Select cell C3 and click on it
• Insert the formula: =9+RANK.EQ(B3,$B$3:$B$23)+COUNTIF($B$3:B3,B3)-1

As we can see in Figure 3, column B has numbers 10-30 with duplicates, because only the RANDBETWEEN function is used. In column C, we have same numbers 10-30, but without duplicates using the RANK.EQ and COUNTIF functions.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

Save time by asking instead! Most questions solved and answered in 10 minutes.

• 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.

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 .

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

You may also be interested in.

• How to select random sample in Excel
• How to sort randomly in Excel
• How to create a sequence of dates in Excel
• How to prevent duplicates in column in Excel

Table of contents

35 comments

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})

Post a comment

• TechRepublic

Account Information

How to generate random numbers with no duplicates in Excel

Share with Your Friends

Your email has been sent

Microsoft Excel can do a lot of cool things. If you need it to generate random numbers, Susan Harkins can show you how.

Image: PixieMe/Shutterstock

Over the last few months, I’ve written about several of Excel’s new dynamic array functions. They’re powerful and easy to implement—relieving us of some very tiresome expression-building that was necessary before. In this article, we’ll combine a few of these new dynamic array functions to generate an array of unique random values. It’s easy to generate random values; it’s just as easy to limit the bottom and top boundaries for a set of random values. What isn’t so easy, as you’ll see, is generating an array of unique random values. Fortunately, using dynamic array functions, you only have to jump through a few hoops.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. Dynamic array functions are supported by 365, Excel for the Web, Excel 2021, Excel for iPad and iPhone, Excel for Android tablets and phones. There’s no demonstration file; you won’t need one.

About random functions

Excel has three random value functions: RAND(), RANDBETWEEN(), and RANDARRAY(). RAND() generates random values between 0 and 1, so random decimal values. RANDBETWEEN() lets you specify the bottom and top values. For instance, you might use RANDBETWEEN(10,1000) to generate random integers between 10 and 1,000.

Finally, RANDARRAY() returns an array of random values using the following syntax: RANDARRAY([rows], [columns], [min], [max], [integer]) Notice that all of the arguments are optional. These arguments allow you to specify the number of rows and columns to fill, the minimum and maximum values to return, and whether to return integers or decimals. The integer argument is false by default, which returns decimal values. To return integers, you must specify TRUE. In addition, min and max are inclusive values. For example, if these two arguments are 1 and 5, respectively, the function will return 1, 2, 3, 4 and 5, not just 2, 3 and 4.

When considering arguments, keep the following rules in mind:

• RANDARRAY() performs like RAND(), returning a value between 0 and 1 if you omit all arguments.
• RANDARRAY() performs like RAND(), returning a value between 0 and 1 if you omit rows and columns.
• RANDARRAY() will return 0 or 1 respectively, if you omit min and max.
• The min argument must be less than max.
• RANDARRAY() will include decimal values if you don’t explicitly set integer to TRUE.
• RANDARRAY() will create the appropriate-sized range to complete the calculations. This range is called the spill range . If there aren’t adequate cells in this range, RANDARRAY() returns a spill error. To learn more about Excel’s spill range, you can read How to use the spill range in Excel
• RANDARRAY() will update results and resize when the source data changes if the source data is in a Table object.
• Currently, if the data source is in another workbook, both workbooks must be open for RANDARRAY() to function properly.
• To generate a random array of non-duplicate values, we’ll use three dynamic array functions: RANDARRAY(), UNIQUE(), and SEQUENCE().

How to generate random values without duplicates, sort of

RANDARRAY() alone has the potential to return duplicate values, as do all the random functions. If you have the time, there’s a simple way to achieve random values with no duplicates. Convert the values in the spill range to static values (also replacing the function) and then remove any duplicates using the Remove Duplicates tool on the Data tab. It is easy, but it isn’t a reasonable way to work unless what you’re doing is a one-time pass.

You can’t force RANDARRAY() to return only unique values, but you can combine it with UNIQUE() to get the desired results. For instance, the following function, shown in Figure A , will return a single column of five (seemingly) unique values between 1 and 20:

=UNIQUE(RANDARRAY(5, 1, 1, 20,TRUE))

Two things might happen though: 1.) You see a list of random unique values that is less than you specified; 2.) You see a spill error (to the right in Figure A ).

When RANDARRAY() repeats enough values that the expression can’t return five unique values from RANDARRAY()’s pool of random values, it will return what it can—which might be short a value or two. The chances of having so many repeating values in that pool of 20 (the min and max values of 1 and 20, respectively) that you can’t return a list of five is small, but not impossible. Press F9 and watch the expression update; eventually you will see an array that is less than five digits.

The shot on the right ( Figure A ) shows the expression in the formula bar where I’ve manually calculated RANDARRAY() by selecting only that function and pressing F9. As you can see, the array does repeat a value, the value 1. Hence, the expression can return only four unique values.

Figure B shows a spill error, spill range unknown . I’ve changed the rows argument from 5 to 19 forcing that to happen more often, but if you press F9 often enough, the rows argument of 5 will eventually return a spill error. It has to do with the volatile nature of these functions—sometimes one calculates faster than the other. A volatile function recalculates every time a change is made.

One way to ensure that the expression doesn’t return fewer values than specified is to create a huge pool of random values. Perhaps the easiest way is to change the rows argument from a specific value to a short expression: n ^2. For example,

=UNIQUE(RANDARRAY(5^2, 1, 1, 20,TRUE))

The row expression, 5^2, will return 25 random values, but now the expression returns up to 25 values (rows) and you’re still susceptible to an occasional spill range error. However, it’s unlikely to return fewer than five rows.

If you can live with all these behaviors, you could stop here. Chances are, though, that you’ll want something more stable.

How to generate random values without duplicates

In the last section, you learned a lot about RANDARRAY() and UNIQUE() and how they work together, even though the results aren’t stable. Your needs will determine whether you need to take this problem to the next level.

Right now, we have an expression that doesn’t always return the right number of values and has the potential to return a spill error. To avoid these two problems, you need a more complex expression in the form

=INDEX(UNIQUE(RANDARRAY(rows^2, columns, min, max, TRUE)), SEQUENCE(rows))

It looks horrible, but don’t worry. Once you understand how it works, it will make good sense, and you’ll have little trouble applying it to your own work.

RANDARRAY() generates an array of random values based on min and max. The rows argument to the power of 2 generates a large pool of random values—much larger than just the rows value itself. UNIQUE() removes all duplicate values and returns an array of unique values. We got this far in the last section.

The difference this time, is that UNIQUE() doesn’t return the array to the sheet. Once UNIQUE() removes duplicates, INDEX() uses the number of rows expressed in SEQUENCE() to determine how many unique random values to return as an array to the sheet.

Figure C shows this expression using our earlier argument values:

=INDEX(UNIQUE(RANDARRAY(5^2,1,1,10,TRUE)), SEQUENCE(5))

First, RANDARRAY() returns a single-column array of 25 random integers between 1 and 10. UNIQUE() removes all the duplicates from those results. INDEX() then returns the top five unique and random values, as determined by SEQUENCE(5).

Keep in mind that min and max still matter. For instance, if min and max are 1 and 5, respectively, you can’t return an array of 10 values—there aren’t that many unique values between 1 and 5. The expression will return five unique values, but the remaining cells in the spill range will display errors. That’s an over-simplification, but it makes the point.

In addition, large arrays take time. Consider a rows value of 2000^2. Internally, Excel is calculating 4,000,000 random numbers! You probably don’t need that large of a pool. Instead, multiply the rows argument by another value, such as 10. That returns 20,000 random values, which might be enough, dependent on the min and max values.

You’ll have to experiment to determine whether a pool is large enough. There’s no true silver bullet because of the relationship: the chance or returning an incomplete spill range increases as the number of random numbers passed to UNIQUE() decreases.

It’s confession time. This is a creative and powerful expression that takes advantage of Excel’s newer dynamic array functions. I’d like to claim full credit for designing this expression, but it has been around the internet now for a while. If I could credit the creative genius who came up with it the first time, I would.

To learn more about dynamic array functions, read the following articles:

• How to use the newish XLOOKUP() dynamic array function in Excel
• How to generate random text using the RANDARRAY() function in Excel
• How to use the FILTER() dynamic array function in Excel
• How to use the spill range in Excel

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

• How to add a prefix to a numbered list in a Word document
• How to display an alert when Outlook email from a specific address arrives
• Cyberattack threat: Corporate users infected via Microsoft Teams
• Checklist: Securing Windows 10 systems
• More must-read Microsoft coverage
• See all of Susan's content

Editor's Picks

TechRepublic Premium Editorial Calendar: Policies, Checklists, Hiring Kits and Glossaries for Download

TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.

7 Best AI Art Generators of 2023

This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.

The Best Cheap Payroll Services for 2023

Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.

NordVPN Review (2023): Pricing, Security & Performance

Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.

Best Free Project Management Software for 2023

Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.

Cloud Strategies Are Facing a New Era of Strain in Australia, New Zealand

Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.

Hiring Kit: Product Manager

Effective product managers oversee the development of a company’s products and act as a liaison between the business, technical and design teams. They help guide cross-functional initiatives and manage activities, tools and relationships across the product development process, from product planning to launch and beyond. This hiring kit from TechRepublic Premium provides a comprehensive framework ...

Payroll Services Comparison Tool

For SMBs, hiring a service to handle payroll calculation, processing and documentation can save valuable time and resources. This comparison chart from TechRepublic Premium provides a framework for comparing potential services. From the download: CHOOSING A PAYROLL SERVICE Payroll processing: This is the primary reason a business will contract with a payroll vendor. The system ...

Inside The Labs: NTT R&D Forum Tokyo 2023

TechRepublic Premium was at the NTT R&D Forum Tokyo 2023, November 14-17. NTT used the event to focus on generative AI, specific service systems, use cases and its IOWN (Innovative Optical and Wireless Network), which was launched in March of this year. In this feature, discover what IOWN means, get news on NTT’s partnership with ...

• TechRepublic on Twitter
• TechRepublic on Facebook
• TechRepublic on LinkedIn
• TechRepublic on Flipboard
• Privacy Policy
• Terms of Use
• Property of TechnologyAdvice

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

* - indicates required fields

Sign in to TechRepublic

Lost your password? Request a new password

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support .

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

Want to receive more TechRepublic news?

You're all set.

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.

Generate Random Numbers in Excel | No Duplicates

If you want to pick participants randomly from a given list or want to get a list of random numbers then this article is for you.

After reading this article you’ll know how to generate random numbers in excel using Random function in excel along with using Randbetween in excel. Let’s get started.

Table of Contents

Using excel Rand function to generate random numbers in Excel

• Select the range of cells. This is where the random numbers will be generated.

Note: Excel Rand function is dynamic, which means it re-runs whenever there is a change in the sheet. So let’s say you enter a value in another cell the formula will re-run and generate random numbers again. To avoid this you should copy and paste the numbers as values so they remain static or constant.

Tip: Once you have used excel random number generator you should check for duplicate values. You can use conditional formatting to highlight duplicates or directly remove them.

Using Randbetween to generate random numbers in excel

The best part about randbetween formula is that it generates random numbers between 2 numbers. Let’s see how we can create excel random number generator using this function.

Note: Excel Randbetween is also dynamic just like Rand function. So make sure after generating the random numbers you copy and paste them as values. Also, always check for duplicates as a precaution.

We hope you liked our tutorial on generate random number excel.

In case you have any follow up questions or want to suggest other topics for us to cover, make sure to add them in the comments below. Take care.

Create Pie Chart in Excel | 3 Quick Steps

Concatenate in excel | 2 easy steps, how to remove space in excel | 3 quick ways, how to remove formatting in excel | clear format in 3 steps, leave a reply cancel reply.

Save my name, email, and website in this browser for the next time I comment.

Advanced Excel Course

Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.

• 10+Hrs of Learning Content
• 50+ Practical Exercises
• Certification
• Doubt Resolution

Blog Contact Us About Us

How to Generate Unique Random Numbers in Excel (No Duplicates)

• -- By Sumit Bansal

I was going through the questions on the Microsoft Excel Community , and one of the questions was on generating random numbers in Excel between 1 to 10, where none of the numbers repeat (i.e., there are no duplicates).

My first instinct was to use the RANDBETWEEN function.

I did a quick check with some random data in Excel, and it failed me – there were repetitions in the results.

Here is what I got when I used the RANDBETWEEN function:

So, I had to resort to the RAND function.

It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions.

If you are generating a lot of random numbers, you can test it once).

Generating a Set of Unique Random Numbers in Excel

Here is how you can use the RAND function to generate a set of unique random numbers in Excel:

• In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
• That’s it. You would have a list of random numbers from 1 to 10, and none of the numbers would repeat.

NOTE: Remember to convert cells with RAND function from formula to values, else it will recalculate and change the list of Random Numbers every time you change anything in the workbook.

Do you use any other technique to generate random numbers with no duplicates?

Let me know in the comments section.

You May Also Like the Following Excel Tutorials:

• Automatically Sort Data in Alphabetical Order using Formula .
• Random Group Generator Template .
• Change Negative Number to Positive in Excel [Remove Negative Sign]
• How to Stop Excel from Changing Numbers to Dates Automatically
• How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas!

FREE EXCEL BOOK

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

16 thoughts on “how to generate unique random numbers in excel (no duplicates)”.

I think RANK now contains a tiebreaker argument so you can guarantee a perfect set of unique numbers with your method.

Only way I found out to do this with zero chance of it repeating was with VBA…your solution probably will work if you’re only doing 10 or something like that.

If it doesn’t, you need to generate a random number, fill it in to the array, and for each next random figure you generate, check to see if it matches any figure in the array; if it does, generate a new number, if it doesn’t, store it and go to the next in the array.

I tested with 50,000 numbers and there is no repetition

To me, this is a very elegant solution that should work well more than 99% of the time. Thanks!

Hello, I need to know, how I can pick random unique no. in my excel. e.g. I have 2000 account and I need 100 account randomly. When I click one button. Highly appreciate your prompt response.

You want to select random records with a click. That would need VBA. Another way could be use randbetween function in a column and then sort the data based on it. Then you can select first 100 records and those would be random.

Another way to do this is join the result of 2/3 of function’s result using “&” sign. This will give more probability of getting unique numbers.

Sir, i have 1 to 10 numbers in column “A” as A1:A10. Then i want 1 number in B1 in one click, then 2 number in B1 in next click, then 3, then 4,…..then 10. But after 10 i want 1 numer in next click. Is it possible in excel please reply me.

Hi Bhimrao,

try this macro (leave first cell blank (means take A2:A11 rather A1:10) and select B2 cell and run the macro)

Sub SerNum3()

If ActiveCell.Offset(-1, 0).Value < 10 Then

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = 1

Regards Hareesh

Dear Sir, you give me answer but when we run macro then only in B2 cell I have 1, then 2, then 3,…..then 10 and after 10 i have reapet from 1, then 2, then 3,…..then 10 again 1 to 10 again 1 to 10 only in B2 cell

what i understood is “for every click excel has to display 1 to 10 numbers and after 10 it is again 1,2,3……..10.

when i checked with same macro it is running as said above.

please send a excel sheet (explaining u r requirement) to my mail “[email protected] i will try to get the answer

regards Hareesh

This is how I do it (same principle)

1. Fill a range of cells with ascending numbers (unique for example: 1,2,3,4…)

2.The fill an adjacent range of cells with randomly generated numbers using RANDBETWEEN

3. Sort the entire range by the second column of random numbers. You will now have unique random numbers.

There is a chance that the RAND numbers will contain a repetition. Thus their RANK would repeat. Regards Brian

Hi Brian.. It should work fine on small data sets. I did some testing on large data sets as well and it came clean. But yes, as I mentioned in the article, there is still a chance, and it is good to check it once

My trick is to put the numbers you want to randomize in column A put the rand function beside them in column B then sort by column B.. Even if there might be a duplicate inColumn b column a will have none.

Good trick.. Thanks for sharing. It is a fine approach when you want the random numbers once. If you need to randomize again, you will have to sort it again.

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Random numbers without duplicates

Related functions .

To return random numbers without duplicates you can use a formula based on the RANDARRAY , SEQUENCE , SORTBY , and INDEX functions. In the example shown, the formula in F5 is:

The result is a list of 12 random numbers greater than 10000, in multiples of 10. By design, the numbers are unique and contain no duplicates.

Note: RANDARRAY and RAND are volatile functions and will recalculate with each worksheet change.

Explanation 

In this example, the goal is to generate a list of random numbers without duplicates. This involves jumping through a few hoops because although the RANDARRAY function can easily generate a list of random integers, there is no guarantee that the numbers will be unique. In the explanation below, we'll look first at a simple option with the RANDARRAY function, then at a more complete solution based on the SEQUENCE function.

Background study

• New dynamic array functions in Excel  - 3 min video
• How to perform a random sort - 3 min video

RANDARRAY option

The RANDARRAY function makes it easy to generate a list of random integers. For example, to generate 12 random numbers between 1 and 100, you can use RANDARRAY like this:

The rows argument sets how many numbers are returned, columns is 1, start is 1, end is 100, and integer is set to TRUE. This formula works fine. However, if you enter the formula and press F9 a few times, you will likely see some duplicate numbers because there is no guarantee that the numbers are unique.

By increasing the range of numbers generated, we can reduce the possibility of duplicates substantially. For example, this formula returns 12 random numbers between 10000 and 50000:

Although there is still a possibility of duplicates, the chance is much lower since there are 40,002 possible numbers. To ensure that there are no duplicates, we can wrap RANDARRAY inside the UNIQUE function like this:

The formula above works well if a specific number of results is not required . However, because the UNIQUE function will remove duplicates if they exist, the final count of numbers returned will change. To work around this problem, and ensure a fixed number of unique random numbers, we can take a different approach with the SEQUENCE function as described below.

SEQUENCE option

Another approach is to use the SEQUENCE function to generate a list of numbers, then use SORTBY and RANDARRAY to sort the list randomly. For example, to output 15 numbers sorted randomly, you can use a formula like this:

SEQUENCE returns an array of numbers between 1-15, RANDARRAY returns an array of 15 decimal values, and the SORTBY function sorts the output from SEQUENCE using the output from RANDARRAY. The result is a list of the 15 numbers between 1-15, sorted randomly.

Worksheet example

An alternative to the simple approach described above is to create a list of unique numbers with the SEQUENCE function , sort the list randomly, then extract a portion of the list. This guarantees a specific number of unique values across a large range of possibilities. In the worksheet shown the formula used to do this in F5 is:

Replacing the cell references with their values, we have:

Working from the inside out, the core of the formula is this:

At a high level, the SORTBY function  is used to sort the output from SEQUENCE randomly. The SEQUENCE function is configured to generate 1000 numbers starting at 10000. The step argument is given as 10 to return numbers that are multiples of 10. The result from SEQUENCE is an array that contains 1000 numbers. The first 10 numbers in the array look like this:

These numbers are returned directly to SORTBY as the array argument.

The RANDARRAY function is set to output 1000 numbers. By default, RANDARRAY will return an array of long decimals. The first 10 numbers in the array will look something like this:

These numbers are provided to SORTBY as the by_array1 argument. With the result from SEQUENCE as array , and the result from RANDARRAY as by_array1 , SORTBY sorts all 1000 numbers randomly and returns the sorted array to the INDEX function . If we refer to this randomly sorted array  as "random_array", we can simplify and rewrite the original formula like this:

Here,  random_array is delivered to INDEX as the array argument. The SEQUENCE function creates an array with the numbers 1-12, and returns this array to INDEX as the row_num argument:

With these inputs, INDEX returns the first 12 rows from the randomly sorted array as a final result. The numbers are guaranteed to be unique because the original array created by SEQUENCE contains no duplicates.

Random names

The approach described above can be applied to other related problems. For example, once you have a list of random numbers without duplicates, you can use those numbers with the INDEX function to create a random list of names without duplicates .

Legacy Excel

Dynamic array formulas are a new feature in Excel . If you have a version of Excel without SEQUENCE, SORTBY, and RANDARRAY, you can take a more manual approach. One method is to enter the core set of numbers to choose from in one column, and use the RAND function to assign random decimal values to each number in another column. Then you can use an INDEX and MATCH formula with the LARGE function to extract random numbers according to their numeric rank. The screen below shows the basic idea:

The formula in F5, copied down, is:

where number (B5:B104) and rand (C5:C104) are named ranges . The LARGE function is used to get the nth largest random decimal number in C5:C104, where n comes from column E. LARGE returns this value to the  MATCH function as lookup_value , which locates the position of the value in the random decimal values. MATCH returns this position to the INDEX function , and INDEX returns the number at that position in B5:B104. The final result is 10 random numbers between 1-100 in F5:F14 without duplicates.

Related formulas

• Random list of names

• Sort by custom list

• Random sort

• Random text values

Related functions

• SEQUENCE Function

The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns  arguments. 

• SORTBY Function

The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.

• RANDARRAY Function

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

• INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

• Dynamic array formulas in Excel
• Alternatives to Dynamic Array Functions
• Dynamic Array Formulas

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.