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

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

    ReplyDelete
  2. Thank you, saved me some headache time.

    ReplyDelete
  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?

    ReplyDelete

Next → ← Prev