Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to pick names out of a hat with Excel

Summary 
In this video, we look at how to simulate a drawing and pick winners in a contest with Excel, using the RAND and RANK functions together.
Video Transcript 

Have you ever had to select the winners in a contest? It's easy when you draw names out of a hat, but how would you do it in Excel?

In this video, we'll show you a simple way to do it using the RAND function.

Here's a list of names that represent entries in a contest. Suppose we want to pick five winners.

In a real drawing, we'd pick 5 random names out of a hat. But how to do that in Excel?

Well, when you use a hat, you can think of the winners as having the lowest numbers in the contest. The first name you draw has a number of 1, the second name has a number of 2, and so on. And if you eventually picked all 100 names out of the hat, each person would have a number.

In Excel, we can simulate this idea using the RAND function. The RAND function generates random numbers between 0 and 1.

For example, if I enter RAND in a cell, we'll see a decimal number.

One thing to know about RAND is that it never stops calculating. Each time we change anything on the worksheet, RAND generates a new number. To stop that from happening, I need to replace the function with the value. Usually, the simplest way to do this is is to use paste special, and values, to overwrite function.

Now we have a fixed value in the cell.

To follow this process for everyone in the contest, we need to generate a random number for each person.

To speed things up, I'll first convert the list names to an Excel table. Now all formulas will be copied down the table automatically, and we'll get structured references as a bonus.

At this point, 5 people already have winning numbers, but we need to lock the values to keep them from changing.

Now you might be looking at list of large decimal numbers and wondering how you're going to choose the lowest values?

Well, we could just sort the list. But conditional formatting is more fun and keeps the values in the same order, so let's use that.

We can use a variant of Bottom 10 items.

Now the 5 winners are clearly marked.

What if you want to see the actual ranking, so that you can assign first prize, second prize, third prize, and so on? For that, you can use the RANK function.

To use rank, you need to supply the number being ranked, a reference to the full set of numbers, and the number 1, to indicate that you're ranking numbers in ascending order.

Now each person in the list has a rank, just like they would if we had drawn all 100 names out of a hat.

This tip was inspired by post Debra Dalgleish wrote about how to pick a winner from a list of names. Debra has a ton of great Excel content on her blog — check it out.

Author 
Dave Bruns

Related shortcuts

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables