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.
Love this Post? Spread the Word!
Comment and share to motivate us to write more!
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

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?


We have Zero Tolerance to Spam. Comments with links will be deleted immediately upon our review.

Next → ← Prev
Scroll to Top