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.
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.
Click the "Data" tab at the top of Excel and click "Data Analysis" in the "Analysis" group.
Click "Sampling" in the "Analysis Tools" list, then click "OK." This brings up a small window called "Sampling."
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.
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.
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.
Click the button with the red arrow in the sampling window to expand the window to its original size and show the other options.
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.
Click the "New Worksheet Ply" button in the "Output options" section to tell Excel to place your sample in a new worksheet.
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.
Tips & Warnings
- You also can click the "Output Range" button in Step 9 and specify a range in which you want Excel to place the random samples.
- You can assign a value to each number in the population by typing a value, such as a name, in the cell next to each number.
- If you cannot access the data analysis tools in Step 2, you may need to install the Analysis ToolPak. Click the Microsoft Office button at the top left of Excel. Click "Excel Options," then click "Add-Ins." Select "Excel Add-ins" in the "Manage" box, then click "Go." Select "Analysis ToolPak" in the "Add-Ins" box, then click "OK." Excel will install the analysis tools, which will be available for you to use.