Sampling in MS Excel

Here are the steps that are needed to take a random sample of observations from within Microsoft Excel. Please note that using spreadsheets to manage data is a generally bad idea... these instructions are here for those folks who don't have any other tools.

Assuming that your spreadsheet has one row of column names (in row 1), do this:

  1. In the first row of the first column to the right of your data, give the sorting column a name, say Sorter. Do not leave any blank columns between the data and the new column.
  2. In the second row of the first column to the right of your data, type =rand(), and hit enter. You need both the = and the ()
  3. Select the cell with the random number in it.
  4. Copy the formula to all the other rows which have data either by:
  5. Once all rows have a random number in them,
    1. Select *all* the data in the spreadsheet, including the first row with the colukn names. (this is critical)
    2. Go to Data->Sort...
    3. Under Sort by, choose Sorter. It does not matter if you sort ascending or descending.
    4. Be sure to check that MS Excel thinks you have a header row, so the column names don't get messed in with the data.
    5. Click OK
  6. If you think you made a mistake in selecting the data, hit Undo immediately.
  7. If you think you are all set, and you won't need to sort any more, delete the column with the random numbers in it.
  8. You can now sample by working your way down the rows in the table.

Things to note:

Send Bill comments about this page or go to his main home page

Last Updated Wednesday, May 10, 2006, 13:54