Pick random names in Excel without duplicates

Step 1: give every entrant a random number

RAND() returns a random decimal from 0 up to (but not including) 1. Give each row its own number and the order of those numbers is a fair shuffle of the list. Picking by rank, not by position, is what stops repeats.

RAND recalculates every time anything on the workbook changes, and every time you press F9. Freeze the numbers before you announce a winner, or the result changes the moment someone types in a cell. Keep the frozen sheet as your record of the draw.

1. In B2, type =RAND() and fill it down to the last entrant (B11 here).
2. With B2:B11 still selected, copy it (Ctrl+C).
3. Paste back as values: Home > Paste > Paste Values, or Ctrl+Alt+V, then V and Enter on Windows. The formulas become fixed numbers, like the ones in this sheet.

Step 2: pull the winners with INDEX, MATCH and LARGE

LARGE(B2:B11,1) finds the largest random number, MATCH finds which row it is in, and INDEX returns the name on that row. Change the 1 to 2 and 3 for the second and third winners, or reference a cell with 1, 2, 3 in it and fill down.

This works in every version of Excel. Ties between RAND values are so unlikely that you can ignore them in practice.

All the winners in one formula (Excel 365 and 2021)

FILTER keeps the rows whose random number is at least the Nth largest, where N is the number of winners in D2. SORT then puts them in draw order, highest number first. Change D2 to 5 and the list grows to five names.

Without SORT, FILTER returns the winners in the order they appear on the list, which is fine for a sample but not for first, second and third prize.

Show each entrant's draw position

For a ranked list instead of a few winners, count how many random numbers are bigger than the row's own and add 1. Fill it down column C to number every entrant from 1 to 10. RANK.EQ(B2,$B$2:$B$11) gives the same answer.

This is also how you split a list into random groups: positions 1 to 5 are team A, 6 to 10 are team B.

The same person entered twice

"Without duplicates" has two meanings. The methods above never pick the same row twice. But if one person is on the list twice, they have two chances and can win twice. Priya Shah appears in rows 2 and 9 here.

Check before the draw: compare the row count with the number of unique names. If they differ, remove the extra entries with Data > Remove Duplicates, or build a clean list with =UNIQUE(A2:A11), then add the random numbers next to that list.

The Excel 365 shortcut and why RANDBETWEEN repeats

In Excel 365 and Excel 2024 you can shuffle and take the top N in one cell with no helper column: =TAKE(SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))),3). In Excel 2021, which has no TAKE, use =INDEX(SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))),SEQUENCE(3)). Both recalculate like RAND, so copy the result and paste it as values to keep it.

Avoid =INDEX(A2:A11,RANDBETWEEN(1,10)) copied into three cells. Each cell rolls its own number between 1 and 10, so two cells can easily land on the same row. With 3 picks from 10 names, that happens more than a quarter of the time.