DISCOVER

# How to create a lottery spreadsheet in Excel

You can organise and track multiple aspects of a lottery system in a Microsoft Excel spreadsheet, including keeping track of winning numbers, recording chosen numbers for participants in the lottery and even choosing numbers at random if you are operating your own lottery number selection system. By combining a few simple tables in an Excel file, you can both organise and create a permanent record of your lottery system.

Define your range of numbers. You can create Excel functions to select random numbers from the range of possible numbers. Open Excel and create a new file, saving it with a name and location of your choice. In the first worksheet, add the range of numbers you want to use for your lottery to the first column. For example, to use the same set of numbers as the UK National Lottery, you would insert numbers from 1 to 49 in column "A," starting from row "1."

Create a list of random numbers. To choose randomly from your selection, insert the following code in the first row of column "B" to choose a random number: \=RAND()

Press "Enter" to generate the random number. Copy the cell by selecting it and choosing "Copy" from the "Copy" list in the "Clipboard" section of the "Home" tab. Select the remaining cells in column B down to the end of the range in Column "A" and paste the formula by choosing "Paste" from the "Paste" list in the "Home" tab "Clipboard" section. You should see a list of random numbers appear in column "B."

Sort your numbers into random order. Select the full range of cells in Columns "A" and "B." Choose "Sort" from the "Sort and Filter" section of the "Data" tab. In the "Sort by" list, select column B. Select "Values" from the "Sort on" list. Your column A values should now be sorted into random order. You can use the first six numbers from this sorted list to select your winning lottery numbers, repeating the process each time you want to choose a new set of winning numbers.

Store a copy of each week's winning numbers. Each time you generate the winning numbers, you can copy the first six from the sorted list into another worksheet to keep a record. Select the first six and copy them, then switch to another worksheet in your file and paste the numbers, adding a weekly indicator such as "Week 1" if you wish.

Track chosen numbers. In the same sheet you use to keep track of the six winning numbers, you can record which numbers players chose for each week. Add a column for each participant and insert their name in the first row. Add their chosen numbers in the rows under this. To make it easier for you to see at a glance whether players have chosen winning numbers, select the numbers in each column and sort them into ascending order, doing the same for the winning numbers.

#### Tip

You could add a row to your spreadsheet indicating whether any players won on a particular week.

#### Warning

Make sure you generate your new winning numbers each week so that you don't use the same set twice.