## Selecting Random Names with Excel

Earlier this month, I got a request from a manager in town asking if I could randomly select 150 people from a membership list. No darts allowed. I think the purpose was to do a survey, but maybe it was to select contest winners. It turns out that Excel has a random number generator which was the key to my solution. *(This tutorial was done with Excel 2007, but the function is present in current versions.*)

While I’m sure other software programs have a randomize function, I chose Excel since that was the file format for this membership list. If you want to get technical, I’m really dealing with pseudorandom numbers. But for my purposes, I wasn’t worried about the differences.

## Excel’s RAND and RANDBETWEEN Function

Excel’s RAND function is a pretty simple function on the surface. As you can see below, simply typing **=RAND()** in a cell produces very small random numbers.

0.19934724 |

0.796184684 |

0.717061354 |

0.32105009 |

0.221891585 |

Personally, I prefer whole numbers which is why I opted to use another Excel Math & Trig function called RANDBETWEEN. You may need to install the Analysis Toolpak add-in.

One advantage to this function is you specify a low and high end range. For example, **=RANDBETWEEN(1,5000)** would generate random whole numbers between 1 and 5000.

With either function, I can add a column to my spreadsheet and create a random value for each row alongside the member’s name. That column can then be used to sort the list.

In the instructions below, I’ve started with a chronological list of US presidents and wish to randomly select 5.

1. Open your Excel spreadsheet.

2. Add a column for the Random number.

3. From the **Tools** menu, select **Options...**

4. Click the **Calculation** tab and switch to **Manual** calculations.

5. In the first cell, type **=RANDBETWEEN(1,5000).** *Note: Choose numbers that produce a large number of choices to lessen the odds of duplicate values.*

6. Click the first cell with the formula in Column B.

7. Press **Shift** and scroll to the last cell you need in Column B.

8. Press **Ctrl+D**. This will copy the formula for your range.

9. Press **F9** to recalculate the random numbers.

10. From the **Data** menu, select **Sort**. Use **Ascending** sort on the **Random** column.

11. From here, I can select how many rows are needed such as 5.

## Cautionary Notes

The main point to remember about using either function is to **turn off Excel’s automatic recalculation**. Otherwise, it’s too easy to have the cell’s update. If that poses a problem, you can copy the Random column to a new column and use Paste Special and Values. You’ll no longer have the underlying formula.

RANDBETWEEN poses another issue if you use small values. As example, I first used =RANDBETWEEN(1,50) figuring the range was larger than the number of presidents. I soon discovered it produced duplicate values.

Although my original task was to randomly select 150 names from an Excel spreadsheet, you can use these functions elsewhere. You could create a spreadsheet with a bunch of nice things to do for others and create your “Random Act of Kindness” list.

Last Updated (Thursday, 25 July 2013 10:27)