Menu Close

Create an Excel Lottery Number Generator

This article looks at how to create an Excel lottery number generator for your weekly lottery numbers.
The steps required to create a lottery number generator in excel are;

  •  
    • Use the RAND() function to generate random numbers
  •  
    • Produce a unique set of random numbers between 1 and 49
  •  
    • Pick 6 numbers at random from the unique set
  •  
    • Insert a button to run the lottery number generator

Contents at a Glance

  1. Set Up the Sheet
  2. Use the RAND Function
  3. Uniquely Rank each Lottery Ball
  4. Select the Ball from the List
  5. Insert a Button to Run the Formulas
  6. Useful Links

Less

Set Up the Sheet 

The initial set up of the sheet looks as below.

Use the RAND Function 

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.

Uniquely Rank each Lottery Ball 

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.

Select the Ball from the List 

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)

Insert a Button to Run the Formulas 

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.