Recently, I got a request from our city manager asking if I could randomly select 150 people from a list of names. 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. Includes an extra function if you’re a Microsoft 365 subscriber.
While other software programs have a random number generator, I chose Microsoft Excel because that was the source file format I received. If you want to get technical, I’m really dealing with pseudo-random number. For my purposes, I wasn’t worried about the differences.
Excel’s RAND and RANDBETWEEN Function
Excel’s RAND function is a pretty simple and popular function on the surface. As you can see below, simply typing
=RAND() in a cell produces tiny random decimal values which don’t appear to fit a normal distribution.
Personally, I prefer a random integer or whole numbers, which is why I chose 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 minimum value and a maximum value for your arguments. For example,
=RANDBETWEEN(1,5000) would generate random whole numbers between 1 and 5000. This is important as you don’t want to duplicate values. 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 spreadsheet column 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 random 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 Function Arguments 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.
What is RANDARRAY?
In 2020, Microsoft introduced another function for Microsoft 365 users called RANDARRAY. It is more versatile than RAND and RANDBETWEEN. For example, in addition to calculating random numbers, the function can also random names from an Excel named range.
Or, if I preferred, I could select random numbers as before. The rows is the number of presidents. And by using TRUE in the Integer field, I will get whole numbers.
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 each time you open the worksheet. 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 these Excel functions elsewhere for all sorts of tasks.