We often need to** randomly select data from our Excel sheet**, especially text data.

**does not offer any dedicated function to select random data. In this article, we will discuss how to randomly select participants in**

*Microsoft Excel***in**

*Excel***4**different ways.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 4 Easy Ways to Randomly Select Participants in Excel

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*### 1. Applying INDEX, RANDBETWEEN and ROWS Functions

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

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

### 2. Using Combination of INDEX, RANDBETWEEN and COUNTA Functions

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*

**Read More: ****How to Randomly Select from a List in Excel (5 Swift Methods)**

**Similar Readings**

**How to Generate Random Date and Time in Excel (3 Ways)****Select a Random Sample from a Population in Excel (3 Ways)****How to Randomly Select Rows in Excel (2 Ways)**

### 3. Applying 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*

**Read More: ****How to Make a Random Selection from List Without Repetition in Excel**

### 4. Applying VBA Code

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

**Read More: ****Excel VBA: Random Selection from List (3 Examples)**

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

**Read More: ****How to Select Random Sample in Excel (4 Methods)**

## Conclusion

In this article, we have talked about **4** easy ways randomly to select participants in** Excel**. This will allow the

**users to randomly select text data from their dataset.**

*Excel*