How to Randomly Select Rows in Excel (2 Ways)

Method 1 – Randomly Select Rows Using RAND Function

We want to select four random rows from the sample dataset.

We will be using the RAND function to assign a random number to each row before sorting them out.

Steps:

• Select cell F5 and add the following formula in the cell.

• Press Enter. It will select a random number between 0 and 1.

• Select cell F5 and click and drag the fill handle icon to fill out random numbers for the rest of the table.

• Copy these values and paste them into the same column to overwrite all the values in it. This will remove the function and the values will stop changing every time you perform any operation.
• Select the whole table, either by pressing Ctrl+A or clicking and dragging manually.
• From the ribbon, go to the Data tab, and under the Sort and Filter group, select Sort.

• A new Sort box will appear. Under the Column, in the Sort by field, select Random Numbers (or whatever you named the column) and under Order select Smallest to Largest (or Largest to Smallest).

• Click OK. This will rearrange the rows of the table according to the random numbers assigned to it.

• Select the first four rows (or the number of random rows you want) and copy and paste it to get a different dataset with random rows.

Method 2 – Applying Formula to Select Rows Randomly in Excel

You can use a formula with a combination of the INDEX, RANDBETWEEN, and ROWS function to select values from a row. This method is especially helpful when you have to select rows from one column or you need to select a value from an array.

Steps:

• Select the cell of the row you want the output(cell D5).
• Add the following formula.

• Press Enter. You will have a random row selected from the list.

Download Practice Workbook

<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel