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 say we want five unique random numbers between 1 and 69.

To do that in Column A, use the =RAND() formula. The RAND Excel function generates a random number. Let’s drag this down to have exactly 69 identical cells (A2:A70).

Now, how to draw the random numbers? You need 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.

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.

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:

1. Create a list of numbers from 1 to 69 in a column.
2. Use the RAND function to generate a random number between 0 and 1 in a separate column.
3. Use the RANK function to rank the numbers based on the random numbers generated in step 2.
4. 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.

Related posts:

How to Fix Missing Formula Bar Issue in Excel How to Cut ✂️ Text in Excel How to Fill Series in Excel