How to Randomly Select Rows in an Excel Spreadsheet

To select a random sample of rows from an Excel workbook, calculate a random number for each row and sort the data on the random number. Then choose from the first row to the number of rows you want in your random sample.

Step

Open the Excel workbook with your data. Click the first row of a new column and type a column header, such as Random Sample. Then click the cell below the header, choose fx, select the Math & Trig category, locate the RAND function and choose OK.

Random Number Function
credit: Steve McDonnell/Demand Media

Step

Grab the lower-right corner of the cell with the random number and drag it down to copy it to the other rows in the workbook with data.

Copy Random Number
credit: Steve McDonnell/Demand Media

Step

Click the upper-left box to select the entire workbook. Choose Data from the menu and then Sort from the ribbon bar. Select the Random Sample column as the sort field and choose OK.

Sort Data
credit: Steve McDonnell/Demand Media

Step

Beginning with the first data row, select the number of rows you want to include in your random sample. For example, if you want 10 rows of data, use rows two through 11. Repeat Step 3 to generate a new random sample.

Select Rows
credit: Steve McDonnell/Demand Media
Paste Values
credit: Steve McDonnell/Demand Media