How to Select Names Randomly in Excel (5 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to randomly select names in Excel, then you are in the right place. During the use of Excel we often need to select names randomly. Excel offers various functions by which we can select those names. In this article we’ll try to discuss how to randomly select names in Excel.


Download Practice Workbook


5 Methods to Select Names Randomly in Excel

Excel offers various methods to select names randomly. To show this, we have made a dataset named List of Players. The dataset is like this.

how to randomly select names in excel


1. Using RAND Function to Randomly Select Names in Excel

We can use the Rand function to select names randomly from a list without duplication.

  • Firstly, write the following formula in the D5 cell like this.
=RAND()

Using RAND Function to Randomly Select Names in Excel

  • Secondly, press ENTER and then use Fill Handle by dragging the cursor while holding it to the right bottom corner of the D5

Using RAND Function to Randomly Select Names in Excel

  • Eventually, we’ll get the outputs in the Random Column like this.
  • This column is needed to select names randomly.

  • Thirdly, write the following formula using the INDEX and RANK functions in the E5 cell like this.
=INDEX($C$5:$C$14,RANK(D5,$D5:$D$14))

Formula Explanation

  • RANK(D5,$D5:$D$14)) → returns the rank of D5 e.0.640365959 among the values of cells D5:D14.
  • $C$5:$C$14,RANK(D5,$D5:$D$14)) →firstly returns the names in cells C5:C14 and then gives the rank of the name in D5 cell i.e. gives the rank of Jane.
  • INDEX($C$5:$C$14,RANK(D5,$D5:$D$14)) → returns the intercept name which happens from the interception of the output value of $C$5:$C$14,RANK(D5,$D5:$D$14)) and the rows from C5:C14.

  • Fourthly, press ENTER to get the output. Additionally, we can use Fill Handle to get other names randomly.

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


2. Utilizing INDEX, RANDBETWEEN and COUNTA Functions to Randomly Select Names in Excel

We can use the combination of INDEX, RANDBETWEEN and COUNTA functions to select names randomly from a list. To show this, firstly, write the following formula in the D5 cell.


=INDEX($C$5:$C$14,RANDBETWEEN(1,COUNTA($C$5:$C$14)),1)

Formula Explanation

  • COUNTA($C$5:$C$14) → returns the number of counts from cell C5 to C14.
  • Output10
  • RANDBETWEEN(1,COUNTA($C$5:$C$14)) → returns any random value between 1 and
  • INDEX($C$5:$C$14,RANDBETWEEN(1,COUNTA($C$5:$C$14)),1) → returns the intercept name which happens from the interception of the output value of RANDBETWEEN(1,COUNTA($C$5:$C$14)) and the rows from C5:C14.

Utilizing INDEX, RANDBETWEEN and COUNTA Functions to Randomly Select Names in Excel

  • Secondly, press ENTER to get the output Milan as a random name.

Utilizing INDEX, RANDBETWEEN and COUNTA Functions to Randomly Select Names in Excel

  • Thirdly, use Fill Handle to get multiple outputs of random selected names.

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


Similar Readings


3. Using INDEX, RANDBETWEEN and ROWS Functions

We can use INDEX, RANDBETWEEN and ROWS functions if we want to select a single random name. We need to write the formula in the D5 cell like this.

=INDEX($C$5:$C$14,RANDBETWEEN(1,ROWS($C$5:$C$14)))

Formula Explanation

  • ROWS($C$5:$C$14)→ Returns number of rows between cells C5 to C14.
  • RANDBETWEEN(1,ROWS($C$5:$C$14))→ Returns a random number between 1 and number of rows.
  • INDEX($C$5:$C$14,RANDBETWEEN(1,ROWS($C$5:$C$14)))→ The number returned by RANDBETWEEN is fed to the row_num argument of the INDEX function, so it picks the value from that row. In the column_num argument, we supply 1 because we want to extract a value from the first column.

Using INDEX, RANDBETWEEN and ROWS Functions

  • Secondly, press ENTER to get the random name as output.

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


4. Applying CHOOSE and RANDBETWEEN Functions to Randomly Select Names in Excel

Excel allows us to get a random number from a range using CHOOSE and RANDBETWEEN functions. We need to write the formula in the D5 cell like this.

=CHOOSE(RANDBETWEEN(1,6), "Jason", "Milan", "Agar", "Austen", "Jane", "Peter")

The parameter bottom of the RANDBETWEEN function is 1 and the top is 6. The result of this function is a random number between 1 and 6 which is the index_num parameter of the CHOOSE function. The values of the function are “Jason”, “Miller”, “Agar”, “Austen”, “Jane”, “Peter”.

Applying CHOOSE and RANDBETWEEN Functions to Randomly Select Names in Excel

  • Secondly, press ENTER to find the output as a random name.

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


5. Using INDEX, RANK.EQ and COUNTIF Functions

We can also use INDEX, RANK.EQ and COUNTIF functions. RANK.EQ function is an improved version of the RANK function.
Firstly, fill up the Random Column by using the RAND function.
Secondly, write the following formula in the E5 cell.

=INDEX($C$5:$C$14,RANK.EQ(D5,$D$5:$D$14)+COUNTIF($D$5:D5,D5)-1,1)

Using INDEX, RANK.EQ and COUNTIF Functions

  • Secondly, press ENTER and use Fill Handle to get all the outputs as random names.

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


Things to Remember

  • If we use the combination of INDEX, RANDBETWEEN and COUNTA functions, we can get random names with duplicates i.e. one name can repeat several times.
  • Using the RAND function gives the random names without duplication.

Conclusion

We can select random names from a list of names if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further query.


Related Articles

Shajratul Alam Towhid

Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo