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.

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.