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’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? 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 to use Paste Special and Values to overwrite the 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 of 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, five people already have winning numbers, but we need to lock the values to keep them from changing. Now you might be looking at this 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 ten items. Now the five 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
