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.

`=RAND()`

• 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).

`=INDEX(\$B\$5:\$B\$19,RANDBETWEEN(1,ROWS(\$B\$5:\$B\$19)))`

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

Breakdown of the Formula:

ROWS(\$B\$5:\$B\$19) returns the number of rows in the range B5:B19 which is 15.

RANDBETWEEN(1,ROWS(\$B\$5:\$B\$19)) returns a random number between 1 and the row number, 15.

INDEX(\$B\$5:\$B\$19,RANDBETWEEN(1,ROWS(\$B\$5:\$B\$19))) returns the cell value from the range B5:B19 depending on the entry taken from the random number generated using the previous functions.

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

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF