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
- Excel Format Numbers in Thousands (K)
- Excel Format Cells Shortcut: Quick Keys for Windows and Mac
- Excel Format as Number: Steps and Common Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Greater Than in Excel: Criteria and ExamplesTo use SUMIF in Excel for values greater than a number, put the comparison in quotation marks as…
- Excel Percentile Function: Rank, Syntax, and ExamplesExcel has separate functions for percentiles and percentile ranks: use PERCENTILE.INC or PERCENTILE.EXC to find the value at…
- How to Use the NOT Function in Excel: A Practical GuideTo reverse a logical value in Excel, use the NOT function: with TRUE in A2, enter =NOT(A2) in…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- Capitalize Names in Excel: Formulas and MethodsTo capitalize names in an Excel column, enter =PROPER(A2) in the next column, replacing A2 with the first…
- How to Multiply Different Cells in Excel With a FormulaTo multiply different cells in Excel, select an empty cell, type a formula that starts with an equals…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.