Often you may want to randomly select names from a list in Excel.
The following examples show how to do so in two different scenarios:
Scenario 1: Select one random name from list.
Scenario 2: Select multiple random names from list.
Let’s jump in!
Example 1: Select One Random Name from List in Excel
We can type the following formula into cell C2 to select a random name from the range A2:A13:
=INDEX(A2:A13,RANDBETWEEN(1,ROWS(A2:A13)),1)
The following screenshot shows how to use this formula in practice:
We can see that the formula randomly selects Frank from the list of names.
Note that we can randomly select another name by double clicking cell C2 and then pressing Enter.
Example 2: Select Multiple Random Names from List in Excel
If we would like to select multiple random names (without duplicates) from a list, then we must first create a helper column that assigns a random decimal value between 0 and 1 to each name.
We can type the following formula into cell B2:
=RAND()
We can then click and drag this formula down to each remaining cell in column B:
Next, we can type the following formula into cell D2 to select the first random name from the list:
=INDEX($A$2:$A$13,RANK(B2,$B$2:$B$13))
We can then click and drag this formula down to as many cells as we’d like depending on the number of names we’d like to randomly select:
We can see that column D now contains 5 random names and none of them are duplicates.
To pick another random list of names, simply click and drag the formula in cell D2 down to however many cells you’d like again.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Generate Random Number Between Range
Excel: How to Randomly Select Cells Based on Criteria
Excel: How to Select a Random Sample
