Pick a Random Value From a List in Excel

by

To pick a random winner, question or name, use =INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8))). It works for a list of any length and gives a new pick each time you press F9.

Any versionINDEX + RANDBETWEEN
Several, no repeatsExcel 365: TAKE + SORTBY + RANDARRAY
New pickPress F9

Method 1: INDEX and RANDBETWEEN

=INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8)))

RANDBETWEEN picks a position from 1 to the number of items, and INDEX returns the item there. ROWS(B3:B8) counts the list, so it adjusts if the list grows. With an Excel Table, use =INDEX(Players[Name],RANDBETWEEN(1,ROWS(Players[Name]))).

List of six contestants
INDEX and RANDBETWEEN picking a random contestant

Method 2: CHOOSE and RANDBETWEEN

=CHOOSE(RANDBETWEEN(1,6),B3,B4,B5,B6,B7,B8)

Works, but you must list every cell, so it's only practical for a handful of items. Use INDEX for anything longer.

CHOOSE picking a random value

Pick several without repeats (Excel 365)

=TAKE(SORTBY(B3:B8,RANDARRAY(ROWS(B3:B8))),3)

Shuffles the list and keeps the first three, so no name is picked twice. See shuffle rows randomly.

Things to know

  • RANDBETWEEN changes after every edit. To keep a result, copy it and Paste Special → Values.
  • Every item has the same chance. For weighted draws, repeat names in the list in proportion to their weight.
  • For a fun on-screen draw, a spin wheel tool is coming to the free tools page.

Related