In this Excel tutorial lesson, you will learn how to prepare a lottery number generator sheet in Excel.
How to generate Random Numbers without Repeats?
Let’s suppose you would like to generate five different random numbers from 1 to 69.
To do that in Column A, use the RAND formula =RAND().
Rand Excel function does just one simple thing. It generates a random number. This is exactly what you need here! Let’s scroll down this to have exactly 69 identical cells (you can choose a different number, it’s up to your needs. Lottery Number Generator will work for you. No worries!.
Now the question is how to draw the random number from that? You need to create a random number list without repeats.
Imagine the simplicity of the trick. You will generate 69 random numbers, and to pick one, just pick the five rows that generated the largest numbers.
Isn’t it clever?
To pick the first random number, just use this Excel formula: =MATCH(LARGE($A$2:$A$70,1),$A$2:$A$70,0)
It will generate the first random number. If it generated 7, it means that the 7th row generated the largest number. You can check that if you like.
To generate more, just change the digit 1 into the next ones (=MATCH(LARGE($A$2:$A$70,2),$A$2:$A$70,0) for second number, =MATCH(LARGE($A$2:$A$70,3),$A$2:$A$70,0) for the third distinct number, and so on).
How to generate Random Numbers without Repeats using RANK function?
To generate random numbers between 1 and 69 without repeats in Microsoft Excel, you can also use the following steps:
- Create a list of numbers from 1 to 69 in a column.
- Use the “RAND” function to generate a random number between 0 and 1 in a separate column.
- Use the “RANK” function to rank the numbers based on the random numbers generated in step 2.
- Use the “INDEX” and “MATCH” functions to retrieve the numbers with the lowest ranks, which will correspond to the random numbers generated in step 2.
Here’s an example of the formula you could use:
=INDEX($A$2:$A$69, MATCH(RANK(RAND(),$B$2:$B$69),RANK($B$2:$B$69), 0))
Where A2:A69 is the list of numbers from 1 to 69, and B2:B69 is the column containing the random numbers generated using the “RAND” function.
This formula will return a random number between 1 and 69, without any repeats. You can copy the formula to multiple cells to generate multiple unique random numbers.
How to generate a single random number?
Let’s generate more. We need one more bonus number between 1 and 26.
How to generate such a number?
There is nothing easier than that with Excel. Excel does have a dedicated function for that. Just use the RANKBETWEEN function:
The Excel formula is =RANDBETWEEN(1,26)
As you probably guessed, 1 is the bottom number and 26 is the top one.
Here is what the whole Lottery Number Generator looks like:
Note: To make sure you get a new set of random numbers each time you recalculate the workbook, press “Ctrl + Alt + F9” to recalculate the workbook. Or you can set the calculation option to “Automatic” so that the workbook is recalculated automatically each time a change is made. To do this, go to “File” > “Options” > “Formulas” and select “Automatic” under “Calculation options”.
How to generate one random number using RAND and ROUND functions?
An alternative method to generate a single random number between 1 and 26 in Microsoft Excel is to use the “RAND” function along with the “ROUND” function:
=ROUND(RAND()*25+1,0)
This formula generates a random decimal number between 0 and 1, multiplies it by 25 to scale it to the desired range (1 to 26), and then rounds the result to the nearest whole number using the “ROUND” function.
You can download a free number generator spreadsheet here.
