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.

## Download Practice Workbook

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

## 2 Ways to Randomly Select Rows in Excel

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.

`=RAND()`

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

**Read More:** **Random Selection Based on Criteria in Excel (3 Cases)**

**Similar Readings**

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

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

- Now press
**Enter**on your keyboard. 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.

`👉`

Finally **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.

**Read More:**** ****How to Generate a Random String from a List in Excel (5 Suitable Ways)**

## 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. For more detailed guides like this visit **Exceldemy.com**.