Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Randomly Select Participants in Excel (4 Easy Ways)

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.


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, RANDBETWEEN, and ROWS functions. Then, we will combine the INDEX, RANDBETWEEN, and COUNTA functions to do the task. After that, we will opt for the RANK function to select random text data. Finally, we will resort to a VBA code to select data randomly. We will use the sample data below to demonstrate the methods.

Sample Data


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.The RANDBETWEN function returns an integer number at random within the range that the user specifies. Finally, the ROWS function returns the row number of a reference.

Steps:

  • To begin with, select the E5 cell and write down the following formula,
=INDEX($B$5:$B$10,RANDBETWEEN(1,ROWS($B$5:$B$10)),1)
  • Then, hit Enter.

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

🔎 Formula Breakdown:

  • ROWS($B$5:$B$10): The Rows function returns the number of rows within a range. Here, the function returns the total number of rows in the range (B5:B10). The result is 5.
  • RANDBETWEEN(1,ROWS($B$5:$B$10)): The RANDBETWEEN function returns any random number within the range that users specify as an argument. Here, the range is between 1 and 5. The 5 value is returned by the ROWS function.
  • INDEX($B$5:$B$10,RANDBETWEEN(1,ROWS($B$5:$B$10)),1): The INDEX function 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 RANDBETWEEN function returns any integer number between 1 to 5 as the row number. That is why the data that the INDEX function will show will also be random data. Finally, the column number is 1 since we are getting the data from one column.

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,the INDEX function, and the RANDBETWEEN function, to select participants randomly in Excel. The COUNTA function counts the number of non-empty cells within a range.

Steps:

  • Firstly, select the E5 and write the following formula down,
=INDEX($B$5:$B$10,RANDBETWEEN(1,COUNTA($B$5:$B$10)),1)
  • After that, hit Enter.

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

🔎 Formula Breakdown:

  • COUNTA($B$5:$B$10): The 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 B5:B10. The result is 5.
  • RANDBETWEEN(1,COUNTA($B$5:$B$10)): The RANDBETWEEN function returns any random number within the range that users specify as an argument. Here, the range is between 1 and 5. The 5 value is returned by the COUNTA function.
  • INDEX($B$5:$B$10,RANDBETWEEN(1,COUNTA($B$5:$B$10)),1): The INDEX function 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 in which data we want to display. The second argument is the row number. Here, the RANDBETWEEN function returns any integer number between 1 to 5 as the row number. That is why the data that the INDEX function will show will also be random data. Finally, the column number is 1 since we are getting the data from one column.

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


Similar Readings


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 the RANK function to display unique random participants. The RAND function generates random numbers. The RANK function ranks a particular number within a list of numbers or an array of numbers.

Steps:

  • Firstly, select the D5 cell and write down,
=RAND()
  • Then, hit the Enter button.

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

  • After that, select the E5 cell and write the following formula:
=INDEX($B$5:$B$10, RANK(D5,$D$5:$D$10), 1)
  • Then, press Enter.

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

🔎 Formula Breakdown:

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

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 VBA code will allow us to randomly select a participant in Excel.

Steps:

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

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

  • In the coding module, write down the following code.
  • Then, save the code.
Sub Select1Random_Participant()
    Dim nRow  As Long
    nRow = [RandBetween(5,10)]
    Cells(5, 5) = Cells(nRow, 2)
End Sub

4 Easy Ways to Randomly Select Participants in Excel

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

4 Easy Ways to Randomly Select Participants in Excel

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

Sample Data

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 Data tab in the ribbon.
  • Then, select the Data Analysis toolbar.
  • Consequently, the Data Analysis dialogue box will appear.

Sample Data

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

Sample Data

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

Sample Data

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

Sample Data

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 Excel users to randomly select text data from their dataset.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo