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()

How to Randomly Select Rows in Excel

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

How to Randomly Select Rows in Excel

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

How to Randomly Select Rows in Excel

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

How to Randomly Select Rows in Excel

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

How to Randomly Select Rows in Excel


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.

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

How to Randomly Select Rows in Excel

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

How to Randomly Select Rows in Excel

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.


Download Practice Workbook


<< 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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo