# 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

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.

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

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