How to Use Excel to Generate Random Samples

You can use Microsoft Excel’s sampling tool to generate a list of random numbers from a population that you specify. For example, you can input a list of possible raffle numbers as a specified population and command Excel to generate a random list of five winning numbers from the population. The sampling feature can generate a random sample only from numerical data, but you can assign a different value to each number in a list of numbers to generate a random list of data, such as random names from a list of conference attendees.

...
Excel can generate a random list of numbers from a specified list.

Step

Determine a range of numerical data in an Excel worksheet from which you want to generate a random sample. For example, generate a random sample from the numbers 1 through 10 listed in the first column in cells A1 through A10.

Step

Click the “Data” tab at the top of Excel and click “Data Analysis” in the “Analysis” group.

Step

Click “Sampling” in the “Analysis Tools” list, then click “OK.” This brings up a small window called “Sampling.”

Step

Click the button with the red arrow next to box called “Input Range” in the “Input” section. This shrinks the sampling window to a single row in which you can enter an input range.

Step

Click and hold the left mouse button in the top left cell of the range of data from which you want to generate a random sample. For example, click and hold the left mouse button in cell A1.

Step

Drag the mouse to the bottom right cell in the range of data, then release the mouse button. This shows the range of cells that contain the population data in the sampling window. For example, drag the mouse to cell A10 and release. Excel shows “$A$1:$A$10” in the sampling window.

Step

Click the button with the red arrow in the sampling window to expand the window to its original size and show the other options.

Step

Click the “Random” button in the “Sampling Method” section of the sampling window and type the number of samples you want Excel to generate in the box titled “Number of Samples.” For example, click the “Random” button and type “10” in the box.

Step

Click the “New Worksheet Ply” button in the “Output options” section to tell Excel to place your sample in a new worksheet.

Step

Click “OK.” Excel generates a list of random samples and lists them in a column in a new worksheet with the first sample in cell A1. In the example, Excel lists random numbers between 1 and 10 in cells A1 through A10 in a new worksheet.