Create an Excel Lottery Number Generator

The initial set up of the sheet looks as below.

The RAND function is used to randomly generate a number between 0 and 1. The function should be entered as =RAND() in cells A2:A50.

The next step is to assign a unique number to each name in the list. The formula below should be entered in cell B2 and copied down to cell B50.
=RANK($A2,$A$2:$A$50)+COUNTIF(B$1:B1,B1)-1

This RANK function is used to rank the number in cell A2 within the range of A2:A50. The second part of the formula is used to create the unique rank number from 1 to 49 and assign it to a lottery ball.

In the cells used to hold the lottery numbers, a VLOOKUP function will be used to return the ball that is assigned the numbers 1 through to 6. The VLOOKUP function will look like below.

=VLOOKUP(Calculations!E$1,Calculations!$B$1:$C$50,2,FALSE)

The formulas in the spreadsheet will recalculate every time you perform an action in Excel. This is not what we want. To gain control over when formulas are performed in Excel, you need to switch the formulas from automatic to manual.

1. Click the Formulas tab on the Ribbon

2. Click the Calculation Options button in the Calculation group

3. Select Manual from the list

No formulas in the workbook will work now until we tell them to. We will create a button to run the formulas.

1. Click the Developer tab on the ribbon

2. Click the Insert button in the Controls Group

3. Select the Button (Form Control) from the list

4. Draw the button onto the worksheet

5. The Assign Macro dialogue box. Click the New button

6. The Visual Basic Editor appears and a module is inserted ready for you to enter some VBA code for your button. Enter the code below to calculate the formulas on the worksheet, replacing the worksheet name and cell references where appropriate

Worksheets("Calculations").Range("A2:B50").Calculate

Worksheets("Draw").Range("C7").Calculate

Worksheets("Draw").Range("E7").Calculate

Worksheets("Draw").Range("G7").Calculate

Worksheets("Draw").Range("I7").Calculate

Worksheets("Draw").Range("K7").Calculate

Worksheets("Draw").Range("M7").Calculate

7. Close the Visual Basic Editor

8. Type "Lottery Number Generator" as the name for your button

The spreadsheet is completed. Click the button to generate your next 6 lottery numbers.

Download the Excel lottery number generator.

Online Excel training tutorials

Online Excel training tutorials on all versions of Excel.

Select a random name from a list in Excel

Select a random name from a list in Excel. Useful for randomly selecting any value from a list.

Simulate a cup draw using Excel

Create a cup draw using Microsoft Excel. The draw is completely random similar to a knockout competition such as the FA cup.