Generating Unique Random Numbers in an interval in Excel

MS Excel has an inbuilt function i.e. RANDBETWEEN which returns a random number between the numbers you specify. The random numbers that this function generates can be repetitive.I found two ways to guarantee that there are no duplicates in a list of random numbers.

Approach 1 : Ranking random integers
Suppose you want 50 non-repeating numbers between 1 and 50.
1. Use RAND() function in cell B6:B55
2  In cell D6, type =RANK(B6,B$6:B$55) and paste it down till cell D55

Download this workbook and practice what you learn.

Approach 2 : Big Daddy Formula
Specify lower limit i.e. 1 in cell J3 and upper limit i.e. 50 in cell J4.

Copy the following formula into cell F6 and paste it down :

Hit CTRL+ SHIFT + ENTER  to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

Download this workbook and practice what you learn.

How to modify
Suppose you want 100 non-repeating numbers between 5 and 105. 
1. Specify lower limit i.e. 5 in cell J3 and upper limit i.e. 105 in cell J4.
2. In F column, paste the formula down till row F105.

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
4 Responses to "Generating Unique Random Numbers in an interval in Excel"
  1. thank you so much

  2. Thank you, saved me some headache time.

  3. I tried the second method in Excel 2003, but the iferror function is not available there. Have you got an equivalent for Excel 2003?


Next → ← Prev