How to Randomly Select Rows in Excel (2 Ways)

Let’s say you want to randomly pick out a customer for a survey, or a giveaway, or you can randomly select some employee to reassign tasks There are so many situations you may need to randomly select values from a dataset in Excel. In this tutorial, I will focus on how you can randomly select rows in Excel.

How to Randomly Select Rows in Excel: 2 Ways

There are two ways to randomly select rows in Excel. There is one that uses the built-in sorting tool in Excel after a bit of modification to the dataset., Then there is another one where you can use a formula formed by various different functions. Each one has its own usage compatibility, so I will be using different datasets for the two methods.

1. Randomly Select Rows Using RAND Function

First, we will be focusing on the sorting method here. For this method, I am selecting the following dataset.

Now, let’s say we want to select random four rows. In Excel, there is a sorting tool that we can use to our advantage here to randomly select rows. We will also be using the RAND function to assign a random number to each row before sorting them out. Follow these steps for a detailed guide.

Steps:

• First, select cell F5 and write the following formula in the cell.

• Now, press Enter on your keyboard. It will select a random number between 0 and 1.

• Then select cell F5 again 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 operations.
• Now, 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).

• After that, click on OK. This will rearrange the rows of the table according to the random numbers assigned to it.

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

2. Applying Formula to Select Rows Randomly in Excel

You can also 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.

The INDEX function takes an array and a row number as primary arguments and sometimes a column number as secondary arguments. It returns the value of the cell at the intersection of the row number and the array.

The RANDBETWEEN function returns a random value within a limit and takes the lower limit and upper limit as two arguments.

The ROWS function takes an array as an argument to return the number of rows in it.

I am using the following dataset for this example containing only one column.

Follow these steps to randomly select rows from datasets like these in Excel.

Steps:

• First, select the cell you want to pick the row, in this case, it is cell D5.
• Then write down the following formula.

• Now press Enter on your keyboard. You will have a random row selected from the list.

Download Practice Workbook

You can download the workbook used in this example with all the datasets used for demonstration included from the box below.

Conclusion

These were the two methods you can use to randomly select rows in Excel. As you can see from the examples the second method can only be useful in lists with only one column. And while using the first method make sure not to copy the random values too for your final output list.

Hope you have found this informative and helpful.

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