MATH-1410

C. Haugen

Random Sampling (without replacement) with Excel

To generate a random sample of size n from a population of size N:

  1. Type the formula =RAND() into cell A1 and then hit Enter or the Tab key.

  1. A random number between 0 and 1 should appear in cell A1. Click on the small black box on the lower right corner of cell A1 and drag the mouse down along column A until you have reached the size of the population. For example, if we want to create a random sample of size 20 from a population of size 400, we would need to drag all the way down to cell A400. Once you reach cell A400, release the left mouse button. A list of 400 random numbers should appear in the first column.

  1. In order to “lock” these random numbers in place, we need to select column A, click on the Home tab, click Copy, then click Paste, and then select Paste Values from the drop-down menu.

  1. The next step is to enter the labels for the members of the population in column B. For simplicity, we can type the number 1 in cell B1, type the formula =B1+1 in cell B2 and then click and drag like we did in column A. Again, for this example, we go 400 rows down.

  1. In order to avoid problems in the next step, we need to lock the values in column B the same way we locked the random numbers in column A (see step #3).
  1. Select columns A and B. Click on the Data tab and then click on the Sort icon in the Data ribbon.

  1. In the Sort dialog box, select Sort by Column A and make sure the Order is from smallest to largest.
  1. Click on the OK button.

At this stage, the data in the first two columns has been sorted according to the entries in column A.

  1. Finally, select the first n entries of column B. These entries represent your random sample. The sample of 20 from this example is shown below. A label has been added above the sample.