To choose a random sample from your data stored in an Excel workbook, you must first assign random numbers to each row. The RAND function can be used to generate random numbers for a cell or group of cells. Once you've inserted a column of random numbers next to your data, you can sort your data in a random order to generate your random sample. Changing the sort order for your data will result in an alternative selection for your random sample.
Open the workbook containing your data collection in Excel.
Insert a blank column next to your data if one isn't already present. For example, right-click on column "A" and then click "Insert."
Highlight all of the cells in column A next to the data to which you want to assign random numbers. For example, if your data is displayed in rows B2 through B50, select cells A2 through A50. Do not highlight cells in a row containing labels for your data.
Type "=RAND()" without the quotes into the Formula bar, and then press "Ctrl-Enter" to assign random numbers to your selection.
Click the "Data" tab to switch to the Data ribbon. Click the "Sort" button in the Sort & Filter group. Click the radio button next to "Expand the Selection" and then click the "Sort…" button to open the Sort dialog.
Select either "Smallest to Largest" or "Largest to Smallest" from the drop-down box under the Order column of the Sort dialog, and then click the "OK" button. Your data will be sorted randomly.
To assign random numbers using a predefined range, use the =RANDBETWEEN function. Type "=RANDBETWEEN(low,high)" without the quotes, replacing "low" and "high" with the low and high numbers designated for your random number assignment. For example, enter "=RANDBETWEEN(1,100)" to generate numbers randomly between the values 1 and 100. Make sure you designate a range that is greater than the number of rows for your data.
Information in this article applies to Microsoft Excel 2013. Steps may vary slightly with other versions or products.
- Jetta Productions/Lifesize/Getty Images