When we planned our 2011 Open House, the Sector staff decided to incorporate a prize draw for everyone who RSVP’d. The challenge was how to design a process that was accurate and quick.
The standard process of drawing names out of a hat required writing each person’s name on individual pieces of paper – time consuming and wasteful! Printing a list of names seemed faster, but wasn’t the most efficient option – as someone would have to use scissors to cut the paper into individual ballots.
So we turned to technology and found a solution using Microsoft Excel that saved paper, ink, effort and, most importantly, time. By saving our original invitation list in an Excel spreadsheet, we were able to apply a formula to randomly select a name from the list.
Today, we are using the same process to select the winner of a Free Ticket to SMC’s Social Media Marketing Workshop in Victoria on October 4, 2012. The draw will be announced on: Wednesday, September 19th, 2012.
How to Create a Paperless Prize Draw using Excel
This tip shows you how to Randomly Select an Entry from a List in Microsoft Excel 2007 and 2010.
1. Designate columns for each type of information by naming each column.
(ie. Column A = First Name, Column B = Last Name, Column C = Company Name, etc.)
2. Enter each person’s information across the row in the appropriate columns.
(ie. Column A = Joe, Column B = Smith, Column C = Sector Learning Solutions, etc.)
3. Create a new column and name it “Draw”. Then populate each row in the Draw column with the formula =RAND()
4. Select the “View” tab then the “Freeze Panes” button (located in the “Window” group). A drop down menu will open – select the “Freeze Top Row” button.
5. Press the “F9” key to refresh your formula to generate a new set of random numbers in the column.
6. Select the first name in the list using your cursor. Then select the “Data” tab and the “Sort” button (located in the “Sort & Filter” group). Next
Sort your data set by the “Draw” column, Sorts on by “Values” and Order by “Smallest to Largest.” Press OK to randomly sort the list of names.
7. The name at the top of the list is the winner!
Note: To reuse the tool on the same list, repeat steps 6 and 7.
This tip was originally brought to you by Gerry Brimacombe and published in our The Dynamic Sector February 2011 newsletter.
Director of Communications,
Sector Learning Solutions