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, let's 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 one 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:
Of course, this spreadsheet is just a tool, and it is not a guarantee that you will win the main prize. Probability for that remains the same unfortunately.
We wish you generated only lucky lottery numbers and won a fortune with our random lottery number generator.
You can download a free number generator spreadsheet here.