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. I didn’t ask. In this tutorial, I’ll show you how to generate random numbers in Excel.
While other software programs have a randomizer function, I chose Excel because that was the source file format I received. If you want to get technical, I’m really dealing with pseudorandom numbers. 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 tiny random numbers.
Personally, I prefer whole numbers, which are 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 RANDBETWEEN 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. That way, the odds of getting the same random number are less.
With either Excel function, I can add a column to my spreadsheet and create a random value for each row alongside the person’s name. That column can then be used to sort the list.
How to Generate 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 Random number column in 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 Excel 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 original 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 randomizer function is to turn off Excel’s automatic recalculation. Otherwise, it’s too easy to have the random number in Excel update. If that poses a problem, you can copy the Random column to a new column and use Paste Special Values. However, 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. However, I soon discovered it produced duplicate number values.
Although my original task was to select 150 names from an Excel spreadsheet, you can use RAND and RANDBETWEEN elsewhere. You could create a spreadsheet with many nice things to do for others and create your “Random Act of Kindness” list.