We often need to randomly select data from our *Excel* sheet, especially text data. *Microsoft Excel* does not offer any dedicated function to select random data. In this article, we will discuss how to randomly select participants in *Excel* in 4 different ways.

In this article, we will learn 4 handy ways to randomly select participants in *Excel*. Firstly, we will use a combination of the ** INDEX**,

**, and**

*RANDBETWEEN***functions. Then, we will combine the**

*ROWS***,**

*INDEX***, and**

*RANDBETWEEN***functions to do the task. After that, we will opt for the**

*COUNTA***function to select random text data. Finally, we will resort to a**

*RANK***code to select data randomly. We will use the sample data below to demonstrate the methods.**

*VBA***Table of Contents**Expand

## 1. Using INDEX, RANDBETWEEN, and ROWS Functions to Randomly Select Participants in Excel

In this method, we will combine three functions to randomly select participants in excel. ** The INDEX function **extracts a value from a table or range, or a reference to a value, and returns it.

**returns an integer number at random within the range that the user specifies. Finally,**

*The RANDBETWEEN function***returns the row number of a reference.**

*the ROWS function***Steps:**

- To begin with, select the
cell and write down the following formula,*E5*

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

- Then, hit
.*Enter*

- Consequently, the cell will show a randomly selected participant.
- You can copy the formula down to select multiple participants.

**🔎 Formula Breakdown:**

:*ROWS($B$5:$B$10)*returns the number of rows within a range. Here, the function returns the total number of rows in the range (*The Rows function**B5:B10**)*. The result is.*5*:*RANDBETWEEN(1,ROWS($B$5:$B$10))*returns any random number within the range that users specify as an argument. Here, the range is between*The RANDBETWEEN function*and*1*. The*5*value is returned by*5*.*the ROWS function*:*INDEX($B$5:$B$10,RANDBETWEEN(1,ROWS($B$5:$B$10)),1)*displays the data at a particular cell. Here, the first argument of*The INDEX function*is a range. This means the function will go through this range to look for the cell whose data we want to display. The second argument is the row number. Here,*the INDEX function*returns any integer number between*the RANDBETWEEN function*to*1*as the row number. That is why the data that*5*will show will also be random data. Finally, the column number is 1 since we are getting the data from one column.*the INDEX function*

## 2. Combining Excel INDEX, RANDBETWEEN, and COUNTA Functions to Randomly Select Participants

In this instance, we will combine ** the COUNTA function** with the previously mentioned two functions,

**and**

*the INDEX function,***, to select participants randomly in Excel.**

*the RANDBETWEEN function***counts the number of non-empty cells within a range.**

*The COUNTA function***Steps:**

- Firstly, select the
and write the following formula down,*E5*

`=INDEX($B$5:$B$10,RANDBETWEEN(1,COUNTA($B$5:$B$10)),1)`

- After that, hit
.*Enter*

- Consequently, you will find a randomly selected participant in the
cell.*E5* - Move the cursor down to select more random participants.

**🔎 Formula Breakdown:**

: The*COUNTA($B$5:$B$10)***COUNTA**function returns the number of cells that are not empty within a range. Here, the function returns the total number of non-empty cells in the range. The result is*B5:B10*.*5*:*RANDBETWEEN(1,COUNTA($B$5:$B$10))*returns any random number within the range that users specify as an argument. Here, the range is between*The RANDBETWEEN function*and*1*. The*5**5*value is returned by.*the COUNTA function*:*INDEX($B$5:$B$10,RANDBETWEEN(1,COUNTA($B$5:$B$10)),1)*displays the data at a particular cell. Here, the first argument of the*The INDEX function*is a range. This means the function will go through this range to look for the cell in which data we want to display. The second argument is the row number. Here,*INDEX function**the*returns any integer number between**RANDBETWEEN**functionto*1*as the row number. That is why the data that*5*will show will also be random data. Finally, the column number is*the INDEX function*since we are getting the data from one column.*1*

## 3. Applying the RANK Function to Randomly Select Participants Without Duplicates

If you want to have multiple randomly selected data without duplication, then this method will be useful for you. Here, we use** the RAND function** and

**to display unique random participants.**

*the RANK function***generates random numbers.**

*The RAND function***ranks a particular number within a list of numbers or an array of numbers.**

*The RANK function***Steps:**

- Firstly, select the
cell and write down,*D5*

`=RAND()`

- Then, hit the
button.*Enter*

- Consequently, you will have a random number in the
cell.*D5* - Next, move the cursor down to the last dataset to fill the column with random numbers.

- After that, select the
cell and write the following formula:*E5*

`=INDEX($B$5:$B$10, RANK(D5,$D$5:$D$10), 1)`

- Then, press
.*Enter*

- As a result, you will get a random participant name in the cell.
- Place your cursor below the number of cells that will correspond to the participants you want to select.

**🔎 Formula Breakdown:**

ranks the data in cell*RANK(D5,$D$5:$D$10): The Rank function*among the cells in the*D5*Then, if we move the cursor down by one, the relative cell reference changes from*D5:D10***D5**to**D6**but the range remains as the range contains an absolute cell reference.ranks the cells gradually, and the*The RANK function*cell gets the rank of the number in the*E5*cell.*D5*The rank of the*INDEX($B$5:$B$10, RANK(D5,$D$5:$D$10), 1):*cell is taken as the*E5*of*row_num*. Since the rank number is a random number, the row number will also be random. Thus the formula returns randomly selected participants.*the INDEX function*

## 4. Using VBA Code to Randomly Select Participants in Excel

In this method illustration, we will resort to a** VBA **code to solve the problem. This

**code will allow us to randomly select a participant in**

*VBA***.**

*Excel***Steps:**

- Firstly, go to the
tab in the ribbon.*Developer* - From there, select the
tab.*Visual Basics*

- After that, in the
tab, click on*Visual Basic*.*Insert* - Then, select the
option.*Module* - Consequently, a coding module will appear.

- In the coding module, write down the following code.
- Then,
the code.*save*

```
Sub Select1Random_Participant()
Dim nRow As Long
nRow = [RandBetween(5,10)]
Cells(5, 5) = Cells(nRow, 2)
End Sub
```

- Finally, go to the
tab and click on it.*Run* - From the drop-down option, select the
command to run the code.*Run*

- Consequently, you will find the name of a randomly selected participant in the
*E5**.*

## How to Use Data Analysis Toolbar to Randomly Select Numeric Data

In the previous methods, we selected text data. In this method, we will use the ** Data Analysis **toolbar to select random numeric data.

**Steps:**

- Firstly, go to the
tab in the ribbon.*Data* - Then, select the
toolbar.*Data Analysis* - Consequently, the
dialogue box will appear.*Data Analysis*

- Afterward, from the dialogue box, select
.*Sampling* - Finally, click
.*OK* - As a result, the
window will appear.*Sampling*

- From the window, select (
*$C5:$C10**)*as.*the Input Range* - Write down
as the*3*under the*Number of samples*option.*Random* - Then, select (
as the*$F5:$F7)*.*Output Range* - Finally, click
.*OK*

- Consequently, you will find 3 random data appearing in the selected range.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

In this article, we have talked about 4 easy ways randomly to select participants in* Excel*. This will allow the *Excel *users to randomly select text data from their dataset.

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