This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Getting a Random Sample

Excel has the ability to generate random numbers from zero to one or from pre-selected number ranges. By using this feature, you can assign a random number to each row in a set of data and sort them randomly.

return to top Getting a Random Sample

When selecting a random sample from a collection of data you must first assign a random number to each row. Then you will copy and paste the random values to another column, which you will then sort in ascending order.

  1. If Column A is not already empty, add two columns to the left of it

  2. In Column A, select the rows of cells you would like to assign a random number to
    EXAMPLE: If your data vertically spans 390 rows, select cells A2 through A390, but do not highlight the title cell A1.
    Selecting Column A

  3. In the Formula text box, type =RAND()
    Random function

  4. Press [Ctrl] + [Enter]
    NOTES:
    Column A
    now displays random numbers corresponding to each row.
    Each cell, however, contains a formula and cannot be sorted.
    Random numbers in Column A

  5. Select all of the cells containing the random numbers that have just been generated

  6. On the Home tab, click COPYCopy button

  7. Place your insertion point in the first cell at the top of Column B

  8. On the Home tab, from the Paste pull-down menu, select Paste Values
    NOTES:
    Column B now contains random numerical values that can be sorted.
    The numbers will not match the values in Column A.
    Example of random values pasted

  9. Delete Column A
    HINT: This column contains the formulas, which are no longer needed.

  10. Excluding title cells, select all of your data
    NOTE: This includes Column A.
    Selecting all data

  11. From the Data tab, in the Sort and Filter group, click SORTSort button
    The Sort dialog box appears.
    Sort dialog box

  12. From the Sort By pull-down menu, select Column A

  13. From the Order pull down-menu, select Smallest to Largest

  14. Click OK
    The Sort dialog box closes.
    Your data is in order by the random numbers.

  15. Select your sample by selecting the number of rows for the desired sample size
    EXAMPLE: Select the first 200 rows.

return to topGenerating a Random Number

The =RAND() function creates a random number between 0 and 1 in the cell(s) you specify.

  1. Select the cell(s) in which you want to create a random number

  2. In the Formula text box, type =RAND()
    Random function

  3. If you have selected only one cell, press [Enter]
    If you have selected multiple cells, press [Ctrl] + [Enter]

return to topGenerating a Random Number within a Designated Range

The =RANDBETWEEN function creates a random number within the range you designate. In the cell(s) you have specified, a whole number will be generated, unlike the =RAND function which generates a decimal.

NOTE: If you are using the =RANDBETWEEN function for multiple cells and you do not want some of the generated numbers to be the same, be sure to designate a range larger than the number of cells you are applying the function to.

  1. Select the cell(s) in which you want to create a random number

  2. In the Formula text box, type =RANDBETWEEN([Bottom],[Top])
    NOTE: Bottom is the number you want to be the lowest in your range, Top is the number you want to be the highest in your range.
    EXAMPLE: To generate numbers between 1 and 50, in the Formula text box, type =RANDBETWEEN(1,50).

  3. If you have selected only one cell, press [Enter]
    If you have selected multiple cells, press [Ctrl] + [Enter]

Excellence. Our Measure. Our Motto. Our Goal.