Easy Excel Random Number Generator

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.

  • 0.19934724
  • 0.796184684
  • 0.717061354
  • 0.32105009
  • 0.221891585

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.

  1. Open your Excel spreadsheet.
  2. Add a column for Order in Column A.
  3. Add a Random number column in C.
  4. From the Formulas menu, select Calculation Options.
Excel Formula menu with Calculation Options highlighted.
Setting Excel Calculation Options.
  1. Select Manual calculations.
  2. In cell C1, type =RANDBETWEEN(1,5000). Alternatively, you may use the Insert Function button and fill out the Function Arguments dialog as below.
Excel function arguments dialog for RANDBETWEEN.Pin
Formula bar with RANDBETWEEN arguments
  1. Double-click the lower-right corner of C2. This will copy the Excel formula down the column.
Copying the RANDBETWEEN formula down column C.Pin
Double-click the lower right corner to copy the formula
  1. In cell A1, type 1.
  2. In cell A2, type 2. This is to establish the sequential number pattern.
  3. Double-click the lower-right corner of cell A2. This should fill in the original order range for your column.
Filling in original sort order in Column A.Pin
Adding in the original sort order
  1. Click the Select All button. It’s the green triangle above the numbered rows.
  2. From the Data menu, select Sort. I opted to go from Smallest to Largest.
Sort dialog with Random column and Smallest to Largest sort order.Pin
Sorting list by Random number value
  1. Click OK.
  2. 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.

Excel RANDARRAY function.Pin
Formula bar with RANDARRAY and 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.

Pin
RANDARRAY Function Arguments Dialog

Cautionary Notes

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.