Generate a Unique List of Random Numbers in Excel With a Simple Formula

Create a list of unique random numbers in Excel with a simple formula that is easy to use and change - this works in older versions of Excel as well as Excel 365.

In this tutorial I show you 4 different formulas for generating unique lists of random numbers in Excel - this includes simple methods and more complex methods that allow you to set the minimum and maximum possible values as well as simple methods for simple lists that just need to appear in random order.

Everything in this tutorial should work for Excel versions back to 2007, or at least 2010, but let me know in the comments if you run into any issues.

As usual, make sure to download the file for this tutorial so you can follow along or just copy/paste the formula and use it as you want.

Excel Courses:

#msexcel #howto #tutorial

[patrickschardt]

Excellent job. Easy to understand.

The cool thing about the Microsoft 365 versions is:
You can have a large set of master data and use INDEX MATCH or other lookup functions to create random smaller data sets

Just match the random number to a position sequence

[davethomas]

Although random numbers are not something I would (typically) use on the job I really appreciate the insights into formula creation. Thank you Don!

[IvanCortinas_ES]

A great and complete tutorial. Thank you for sharing it!

[darrylmorgan]

Some Really Cool Formulas Excellently Explained!Thankfully Microsoft 365 Makes Complex Formulas So Much Easier...Thank You Sir :)

[wayneedmondson]

Nice! Cool tricks. Thanks for sharing :)) Thumbs up!!

[UmuokpalaOcha]

It can be a combination of 2, 3 and 4 digit code

[ajpran]

thanks sir, it's works good,
but what if with exclude number ?

[Excelambda]

Great video! Old and new school. A trick that I use when I generate random nr. to make them stop but keep them where they are. For ex:
in B2 put "y", in cell C2 =IF(B2="y", RANDARRAY(10, , 1, 100, 1), C2#) . If we change B2 with "n", will stop recalculating. New set of data? back to "y". Hit F9...F9....Like to keep a particular set, change B2 to "n" .
Obs. If circular reference error, we need to go in Options, Formulas, tick ..Enable iterative calculations.
Also could be cool to generate random words, random list selection.😉✌✌

[Kolian]

Hello dear
Ihave 10 deferent numbers as Inout
I want to see how many random number I can create from that 10 number
For example my number is
03, 05, 07, 11, 13, 15, 17, 19, 21, 23
Find out how many random number I can creat using above number
Thanks

[yawsarpong]

Please help me out; my English is poor but I hope you will understand me afterwards. I have 45 numbers and I want to group them into 3, which is 15 each. Now how can I you use 15/30 randomly.

[UmuokpalaOcha]

I want to generate 4 digit unique code

[kathyroberts]

I hope somebody understood what you said, I am sorry I was not one of them! Wayyyy too advanced for me.