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.

Article Contents

## 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.

- 0.19934724
- 0.796184684
- 0.717061354
- 0.32105009
- 0.221891585

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.

## 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. 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.