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 1

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.

(Image: Steve McDonnell/Demand Media)

Step 2

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.

(Image: Steve McDonnell/Demand Media)

Step 3

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.

(Image: Steve McDonnell/Demand Media)

Step 4

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.

(Image: Steve McDonnell/Demand Media)


  • It might appear that the sort didn't work properly because the data won't be sorted by the random number column. This is because the random numbers are formulas, and after Excel sorts the data, it recalculates new random numbers and puts those in the random number column. To preserve the original random numbers, copy the data in the column and paste it back into the same column using the Special option with Paste Values before you sort the data.

(Image: Steve McDonnell/Demand Media)
Promoted By Zergnet


You May Also Like

Read Article

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!