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 Microsoft Excel has a random number generator which was the key to my solution.

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 Excel 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 Excel 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.

- Open your Excel spreadsheet.
- Add a column for the Random number.
- From the
**Tools**menu, select**Options…** - Click the
**Calculation**tab and switch to**Manual**calculations. - 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.* - Click the first cell with the formula in Column B.
- Press
**Shift**and scroll to the last cell you need in Column B. - Press
**Ctrl+D**. This will copy the formula for your range. - Press
**F9**to recalculate the random numbers. - From the
**Data**menu, select**Sort**. Use**Ascending**sort on the**Random**column. - 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 cells 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.** For 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.

### Want More Tips & Tools?

Several times a month, we also publish a free newsletter with relevant stories, tips and special offers.