Generating Unique Random Numbers in an interval in Excel

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 20% off till July 14, 2017

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 close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


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

    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