Earlier this month, I got a request from our city manager asking if I could randomly select 150 people from a mailing 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 has an Excel random number generator. This function 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.
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 of 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. This is important as you don’t want to duplicate entries. It’s best if you choose a top value much higher than what you need.
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.
How to Create Random Numbers
In the instructions below, I’ve started with a chronological list of US presidents and wish to randomly select 5 based on the smallest value of a random number I’ll create.
- Open your Excel spreadsheet.
- Add a column for Order in Column A.
- Add a column for Random in Column C.
- From the Formulas menu, select Calculation Options.
- Select Manual calculations.
- In cell C1, type =RANDBETWEEN(1,5000). Alternatively, you may use the Insert Function button and fill out the dialog as below.
- Double-click the lower-right corner of C2. This will copy the RANDBETWEEN formula down the column.
- In cell A1, type 1.
- In cell A2, type 2. This is to establish the sequential number pattern.
- Double-click the lower-right corner of cell A2. This should fill in the orginal order range for your column.
- Click the Select All button. It’s the green triangle above the numbered rows.
- From the Data menu, select Sort. I opted to go from Smallest to Largest.
- Click OK.
- From here, I can select how many rows are needed such as 5.
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 random values 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.
Disclaimer: Images from Amazon Product Advertising API. This site may receive an affiliate commission on these products. Updated: 2020-09-14