Excel Formula for Choosing a Random Name from a List

Formulas & functions · Updated November 19, 2026

With names in A2:A20, enter =INDEX($A$2:$A$20,RANDBETWEEN(1,ROWS($A$2:$A$20))) in an empty cell to choose one at random. Excel returns a name from that range; press F9 to draw again, or copy the result and paste it as a value if you want it to stay fixed.

The basic formula and a quick example

The formula works in Excel for Microsoft 365 on Windows and uses two standard functions: INDEX returns an item from a range, and RANDBETWEEN generates the position to return. The first argument to INDEX is the list; the second is a row number within that list.

With names in A2:A6, in this order—Maya, Luis, Priya, Omar, and Chen—the formula is:

=INDEX($A$2:$A$6,RANDBETWEEN(1,ROWS($A$2:$A$6)))

For this layout, RANDBETWEEN produces an integer from 1 through 5, and INDEX returns the name in that position. If the generated position is 3, the result is Priya. That is one possible result, not a result Excel will guarantee on every calculation.

Put the formula outside the source list so it does not replace a name. The dollar signs make the range absolute: if you copy the formula to another cell, the source range remains A2:A6. They are optional if you are entering the formula in just one cell.

How the formula chooses a name

RANDBETWEEN(1,5) can produce any whole number from 1 to 5, including both endpoints. INDEX then treats that number as the position in the range: 1 means the first cell, 2 the second, and so on. Each row position has the same chance of being selected, provided the range contains the same number of eligible entries and no blanks are accidentally treated as entries.

ROWS counts how many rows are in the selected range. Using it for the upper limit means you can change the range in the formula and its row count will adjust with it. It does not automatically discover names added below the range, however. If the list grows beyond A2:A20, expand the reference or use a table-based range.

This is a random selection by position, not by distinct spelling. If “Maya” appears in three rows and each other name appears once, Maya has three chances in the draw. Remove duplicate entries if each person should have equal odds, or keep them if repeated rows are intentionally meant to increase someone’s chance.

Set up the list so the result is reliable

For the basic formula, keep the names in one continuous column with no blank cells inside the selected range. A heading such as “Name” belongs above the range, not inside it. If the heading is included, it can be selected just like a name.

  • Place the list in a single column, for example A2:A20.
  • Check for blank rows within the range and remove them or use the blank-safe formula below.
  • Include each person once when every person should have equal odds.
  • Put the selection formula in a separate cell, such as C2, and label it “Random pick” if the worksheet needs explanation.

A fixed range is often enough for a short list. For a list that regularly grows, format the data as an Excel table using Insert > Table, then include new names as table rows. A table can make the source easier to maintain, but the formula still needs to refer to the name column rather than a heading or unrelated data.

Choose from a range that contains blank cells

The basic formula can land on a blank cell if the selected range has gaps, so the result may appear empty even though names are present. In Excel for Microsoft 365, FILTER can first create a list of nonblank cells, and LET can give that filtered list a readable name.

With names in A2:A20 and possible blank cells within that range, use:

=LET(names,FILTER(A2:A20,A2:A20<>""),INDEX(names,RANDBETWEEN(1,ROWS(names))))

This filters out empty cells before selecting a position. If the nonblank names are Maya, Luis, and Chen, for example, the filtered list has three rows and each of those rows can be picked. If every cell is blank, there is no eligible name and the formula returns an error; enter at least one name before drawing.

FILTER and LET are available in Microsoft 365 and Excel 2021 or later. In older Excel versions, remove gaps from the list or copy the nonblank names into a continuous helper range and use the basic formula on that range.

Get a new pick or keep the current one

RANDBETWEEN is a volatile function: Excel can recalculate it when the workbook recalculates, not only when you edit the name list. A selection may therefore change after you press F9, change a related cell, or reopen the workbook. With automatic calculation enabled, some other worksheet edits can also trigger recalculation.

To request another selection, press F9 on Windows. On a Mac, use Fn+F9 if the function keys are assigned to hardware controls; the exact key behavior depends on your keyboard settings. You can also use Formulas > Calculate Now in Excel. In manual calculation mode, a formula may not update until you request calculation.

To preserve a result, copy the cell and use Home > Paste > Paste Values. This replaces the formula in the destination with its current displayed text. You can also copy the formula cell and paste values over itself, but keeping a separate copy is safer if you may need to draw again. If you want both a fixed winner and a live draw cell, paste the value into a different cell.

Pick several names without repeating anyone

Using the basic formula in several cells does not guarantee different names. Each cell makes its own draw, so the same person can appear more than once. That can be useful for repeated independent draws, but it is not a “draw without replacement” method.

In Microsoft 365 or Excel 2021 and later, SORTBY and RANDARRAY can randomize a whole list. With unique names in A2:A20 and no blank cells in that range, enter this in an empty area:

=SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20)))

The formula spills a randomly ordered version of the list into the cells below it. Take the first few names in that result to select several people without repeating a source row. The order can change when Excel recalculates, so paste values if you need to record the draw. If the source itself contains duplicates, the shuffled output will still contain those duplicate entries.

For a single draw at a time where the same person must not be selected again, record each selected name and remove or exclude it from the eligible list before the next draw. That approach is easier to audit than assuming several independent formula cells will produce unique results.

Common problems and what to check

  • The cell looks blank: Check whether the selected range has blank rows. Use the FILTER version or make the source list continuous.
  • The heading was selected: Start the reference below the heading, such as A2 rather than A1.
  • The formula returns an error: Confirm the range contains at least one eligible name and that the formula refers to valid cells.
  • The answer keeps changing: This is expected for a volatile random formula. Paste values to record one result.
  • The same person appears more than once: Multiple independent formulas can repeat a name. Use a shuffled list when selecting several entries without replacement.
  • Someone has better odds: Look for duplicate rows. Each row is a possible outcome, so duplicated names receive extra chances.

If Excel shows a formula error immediately after entry, check that the formula uses commas between arguments, as shown here. Some regional Excel settings use semicolons instead of commas as the argument separator; use the separator expected by your installation if Excel rejects the comma version.

Fairness, randomness, and appropriate uses

For an ordinary classroom activity, team assignment, or informal prize draw, a worksheet formula can be a convenient way to select from a list. Its fairness depends on the source list being correct and on each eligible person having the intended number of entries. Decide in advance how to handle duplicates, blank rows, people who are ineligible, and repeat selections.

Excel’s random functions are designed for spreadsheet calculations, not for cryptographic security or independently verifiable high-stakes drawings. A formula result can recalculate, and a workbook can be edited. For a consequential or regulated selection, use an appropriate process with clear rules, a preserved record, and any required oversight rather than relying on a changeable cell alone.

Frequently Asked Questions

What is the simplest Excel formula to pick a random name?
With names in A2:A20 and no blanks in that range, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). It chooses one row position and returns the name in that position.

How do I make Excel choose a name again?
Press F9 on Windows, or use Fn+F9 on many Mac keyboards, to recalculate. Formulas > Calculate Now is another option. The name may also change when Excel recalculates for another reason.

How do I stop a random name from changing?
Copy the result cell and choose Home > Paste > Paste Values. The pasted text no longer depends on the random formula.

Can Excel pick multiple random names without duplicates?
Yes. With a nonblank list in A2:A20, use =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))) in Microsoft 365 or Excel 2021 and take the first required entries from the spilled shuffled list. Paste values to preserve that order.

Why does Excel sometimes return a blank instead of a name?
The random position may point to an empty cell inside the selected range. Remove the gaps, select a continuous range, or use FILTER to exclude blank cells before drawing.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.