Tag Archives: Excel

How to Create a Paperless Prize Draw using Excel

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.

-Kelly Marshall,
Director of Communications,
Sector Learning Solutions

Subtotals in Excel

As some of you may already know, Sector Learning recently delivered a one-hour Excel 2007/2010 Tips and Tricks overview session to the Greater Victoria Chamber of Commerce for their Business Education Series.

One of the questions stemming from this seminar had to do with creating subtotals in Excel. After trying to tackle this one on my own, I decided to seek out some assistance from one of our expert instructors. Linda Swanton to the rescue! Rather than simply explaining in words how to go about doing this, Linda kindly took the time to create an Excel spreadsheet to show how this can be done.

Question: How do I put subtotals and totals in the same column of an Excel spreadsheet?

Answer:

Aahh, the power of visuals!

Don’t forget – whether it was last week, last month, or last year, if you’ve ever taken a class with Sector Learning  you have unlimited lifetime access to our Ask-An-Instructor forum. Our instructors are here to help! We will do our best to address your questions as quickly (2-3) days as possible.

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions