How to pick names out of a hat with Excel

Abstract

Have you ever had to go for the winners in a contest?

In this video we’ll show you a simple way to do it using theRAND function.

Here’s a list of names that represent entries in a contest.

Suppose we’d like to pick five winners.

In a real drawing, we’d pick five random names out of a hat.

But how do we do that in Excel?

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 theRAND 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 to usePaste Specialand Values to overwrite the function.

Now we have a fixed value in the cell.

To speed things up, I’ll first convert the list of names to an Excel table.

Now all formulas will be copied down the table automatically, and we’ll getstructured referencesas a bonus.

Well, we could just sort the list.

Butconditional formattingis more fun and keeps the values in the same order, so let’s use that.

We can use a variant of Bottom ten items.

Now the five winners are clearly marked.

For that, you’re able to use theRANK function.