How to Randomly Select Rows in an Excel Spreadsheet
Select a random sample from an Excel workbook by calculating a random number for each row, sorting by it and then selecting the number of records you need.
Things You'll Need
- Microsoft Excel
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.
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.
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.
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.
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.
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.