How to Create Random Numbers in Excel (including no repeats)

In this video I’ll show you how you can easily create sample data in Microsoft Excel. Generating Random data is often needed for testing or if you need to share a file with someone who shouldn’t see your actual data. I’ll cover different methods to create random numbers including a solution that creates random data without repeats.

00:00 How to Generate Random Numbers in Excel
00:29 How to Use the RAND Function
01:15 How to Use the RANDBETWEEN Function
02:05 How to Use the RANDARRAY Function
02:59 How to Create Random Numbers WITHOUT Repeats
04:11 Use Sequence function to get Avoid Repeats & errors
05:54 Non Office 365 Solution to Avoid Repeats
06:44 Wrap Up

★★★ My Online Excel Courses ►

⯆ DOWNLOAD the workbook here:

LINKS to more Excel videos:

★ Get the Official XelPlus MERCH:

✉ Not sure which of my Excel courses fits best for you? Take the quiz:


GEAR I use:

More resources on my Amazon page:

Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!



  1. Its to bad its not true random

  2. You are awesome. You have really helped me a lot I am impressed how these small changes can now help me with my data

  3. Hi… Plz guide how to concatenate two cells by keeping the text color format?

  4. Hi Leila I have been following you for about 18 months thank you so much for taking my Excel knowledge to the next level

  5. Just be careful of nsa backdoor.. 🙂

  6. How to create a random password with alphanumeric & special character and id number please tell

  7. Handy tips as always Leila, very intuitive, thanks for sharing

  8. Great job! But Same topic already available.

  9. Does this works in Excel 2016 also?

  10. Put accounts budgeting and forecasting video if you put already please give me the link mam you videos are amazing mam

  11. At laaaast, I saw one video of yours where I did not find anything new!!!!!!!!!!!!!!!!!!!!!!!!!!

  12. Thank you, Leila for all of these amazing tutorials. They are very much appreciated! 🙂

  13. Do you have a playlist related to Excel in which you have completed excel from scratch? plz reply asap, I like your videos by heart

  14. How does one become a professional in excel like you???

  15. Leila, Did you just try to get me to invest in Bit Coin? I replied to WhatsApp request and all I got was an attempted Bit Coin pitch.

  16. Thank you for all your great videos they’ve been a great help! Can I ask you a question about another topic? You may have a video on this but I haven’t been able to find it. Let me know if I should post by question here or just send you an email thanks.

  17. These are some interesting functions but a "random" list that requires no repeats is not random. Useful perhaps but not random.

  18. If it's including no repeats then it's not random

  19. Hi Leila. Great tips! One weakness of UNIQUE(RANDARRAY()) is that it might produce say less than the 10 desired numbers, if UNIQUE eliminates one or more duplicates. You can get around this by having RANDARRAY produce more than 10 results (say 20) and then use INDEX and SEQUENCE to output just the first 10. Here is an example: =INDEX(UNIQUE(RANDARRAY(20,,100,1000,1)),SEQUENCE(10)). So, even if UNIQUE eliminates one or two duplicates from the 20, there are still more than 10 for INDEX/SEQUENCE to output which gives you a certain list of 10 results vs. sometimes getting only nine or eight. Of course, it is not perfect.. still subject to #SPILL and you must be sure to build in enough extra results from UNIQUE(RANDARRAY()) to not drop below the desired number of output items. Nevertheless.. another way to get there. As always, thanks for sharing your tips :)) Thumbs up!!

  20. What tests of randomness does this generator pass?

  21. Do you have plans to do a Power Automate Desktop course?

  22. You are the greatest, Leila. I had not thought of using unique with randarray yet.

  23. Great tutorial as always. Thanks for sharing

  24. How you edit your video so nice can you please make a video for that

  25. 🌹🌹💜thanks miss

  26. I want to know about tableau

  27. Just use consequence of digits of number π.😁

  28. Rand() function works randomising values from a uniform distribution. But what about getting random numbers from another kind of distribution – normal, Poisson, etc?

  29. We can get random number by using data analysis tool in excel. Add data analysis tool by add ins, then select random number generator – select uniform – give min and Max number to get data in Excel

  30. Leila- I have a list of 10 companies and each company has 5 or more contacts. I wish to assign the same number to the first contact of each company. For example- Assign #1 to the first contact of each company. Assign #2 to the second contact of each company. So on and so forth for the rest of contacts

  31. Interesting & well explained !! Can you share how to put random dates ?

  32. Your tutorials are totally worthwhile. I don't know if I will ever need this, but this video is so memorable that I will recall it if and when the need arises..!

  33. Cute technique. Thanks for sharing ☺️

  34. Thank you Leila. Finally! A useful formula to help me pick the random winning Powerball numbers 😁

  35. Great for Monte Carlo simulations.

  36. Hello sir, I have a problem with a large database in which there are repeated names, but the repetition is partial and does not appear in the normal conditional format. Is there a way to find the partial repetition

  37. Great and sweet…!!!

  38. Hi Leila.. would you suggest for data analysis which is better.. MAC or Windows..

Leave a Reply