Excel Formula to Randomly Select From a List

Formulas & functions · Updated November 19, 2026

With names in A2:A20, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) to return one randomly chosen entry. The formula selects a row position from the list and returns the value at that position; it works in Microsoft Excel for Microsoft 365 on Windows and in many earlier Excel versions too.

How the random selection formula works

The formula combines three functions. ROWS counts how many cells are in the specified range, RANDBETWEEN returns an integer between two inclusive limits, and INDEX returns the value at the row position it is given. For A2:A20, there are 19 cells, so the random position can be any integer from 1 through 19. The first position means A2, the second means A3, and the nineteenth means A20.

For a small example, suppose A2:A5 contains Cedar, Maple, Birch, and Ash. With those four entries in A2:A5, this formula selects a position from 1 to 4 and returns the corresponding name: =INDEX(A2:A5,RANDBETWEEN(1,ROWS(A2:A5))). A possible result is Birch, if the generated position is 3. The formula does not guarantee any particular result, and each entry has the same chance when each cell is one list item.

Because the formula uses a position rather than comparing the text in the cells, it can select words, numbers, dates, or other values. If the same value appears in multiple cells, each occurrence is a separate chance to be selected. For instance, a list containing “Red” twice gives Red two chances, not one.

Set up the list and enter the formula

Put the items in one continuous column or row, with no title mixed into the range you intend to sample. In the example, A1 could contain the heading “Name” and A2:A20 the entries. Enter the formula in a separate cell, such as C2, so it does not overwrite the source list. If your list is in another location, replace both references to A2:A20 with the actual range.

  1. Check that the range contains only the entries you want included.
  2. Select an empty result cell, such as C2.
  3. Type the formula, updating its range if necessary, and press Enter.
  4. To make another draw, recalculate the workbook or trigger a recalculation by editing a cell.

Excel formulas generally use commas between arguments in the examples here. Some regional settings use semicolons instead; if Excel flags a comma as invalid, use the argument separator configured for your installation. The function names and the logic remain the same.

What happens when the formula recalculates

RANDBETWEEN is a volatile function: it can generate a fresh result when Excel recalculates. As a result, the selected item may change after you edit a cell, open or recalculate the workbook, or change a value that causes calculation. This is expected behavior, not evidence that the list has been edited.

On Windows, press F9 to recalculate formulas in open workbooks. On a Mac, the equivalent may be Fn+F9, depending on the keyboard and system function-key settings. If results are not updating as expected, check that calculation is set to Automatic in Excel’s calculation options. Recalculation can affect other formulas in the workbook as well, not just the random choice.

Repeated recalculation does not make the result a reliable record of a past draw. If the chosen item must stay fixed—for a classroom assignment, a meeting agenda, or a one-time selection—copy the result cell and paste it as a value. On Windows, use Ctrl+C, then Home > Paste > Paste Values. On Mac, use Command+C and choose Paste Values from Excel’s paste options. After converting the result to a value, it will no longer update with the random formula.

Choose whether blanks should be eligible

The basic formula treats every cell in its range as an entry, including empty cells. If the range has blank cells, a draw that lands on one may appear as a blank result. The formula also counts cells that contain formulas returning an empty string as positions. To avoid blank choices in a list that may have gaps, Excel for Microsoft 365 and Excel 2021 can filter out empty cells first.

With possible blanks in A2:A20, use this formula to choose only cells that are not empty: =INDEX(FILTER(A2:A20,A2:A20<>""),RANDBETWEEN(1,ROWS(FILTER(A2:A20,A2:A20<>"")))). For example, if A2:A20 has 19 cells but only 16 nonblank entries, the random position is chosen from 1 through 16. One possible result is the value in the third nonblank cell, even if that cell is not physically A4.

FILTER requires Excel 2021 or Microsoft 365. If every cell in the range is blank, this formula returns an error because there is no item to select. Remove the blank rows, make sure at least one entry exists, or add an explicit error-handling approach if an empty list is a normal possibility in your workbook. Also decide whether a cell containing a space should count as blank: it is not truly empty, so this test will include it.

Draw several items with or without repeats

Copying the basic formula into several cells makes independent random draws, but it does not prevent repeats. With names in A2:A20 and formulas in C2:C6, the same name can appear more than once. That is useful when each draw is meant to be independent, such as selecting a random prompt repeatedly where repeats are allowed.

For five different source positions without replacement, Excel 2021 and Microsoft 365 can sort the list by random numbers and return the first five values. With the items in A2:A20, enter this in C2: =INDEX(SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))),SEQUENCE(5)). In current dynamic-array versions, the five results spill into the cells below C2. It draws five positions from the 19 source rows, so no source row is selected twice in that result. The output order is randomized too.

This method avoids repeated positions, but it cannot guarantee distinct text if the source itself contains duplicate values. If A2 and A3 both contain “Cedar,” selecting both rows will show Cedar twice. The number in SEQUENCE(5) is the requested draw count; it must not exceed the number of eligible source rows. When using this formula with blanks, filter the source range first so blank rows are not part of the shuffle.

Dynamic-array formulas need room to display their results. Clear the cells below the formula before entering it; otherwise Excel may show a spill error. The order can change again after recalculation, just like the single-item formula.

Common problems and useful checks

  • A blank result: Check for empty cells inside the selected range. Shorten the range or use the filtered formula if you want to ignore blanks.
  • An unexpected item: Confirm the range starts and ends at the intended cells. Including a heading, subtotal, or extra row makes that cell eligible too.
  • Repeated results: Repeats are possible with the single-item formula and with multiple independent copies. Use a shuffle-and-take approach when sampling without replacement.
  • The result keeps changing: That is normal for a volatile random formula. Paste the result as a value when you need to preserve a draw.
  • A formula error: Confirm that the range is valid, the list has at least one eligible item, and your Excel version supports any newer functions used.

One important distinction is between selecting a value and selecting a unique value. The standard formula gives each cell an equal chance; it does not remove duplicate entries or assign equal chances to distinct labels if some labels occur more often. If every name should have equal odds, first make sure each name appears once in the source list.

Randomness, fairness, and appropriate uses

Excel’s random functions are designed for ordinary spreadsheet uses such as shuffling a list, choosing a sample for review, or picking a discussion topic. They are not a substitute for an auditable drawing process or a cryptographically secure random generator. For a high-stakes selection, use a process whose rules and results can be independently reviewed, and document the source list and selection procedure.

For a casual classroom activity, it can be enough to display the list and formula, then paste the selected value before continuing. For a business sample, consider preserving a copy of the original data and the selected records, so later recalculation does not obscure what was chosen. Keep in mind that a formula’s visible result alone does not establish how a selection was conducted or whether the input list was complete.

Frequently Asked Questions

How do I randomly select one name from a list in Excel?
With names in A2:A20, enter =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))) in an empty cell. The formula returns one entry, and it may change when Excel recalculates.

How can I stop Excel’s random choice from changing?
Copy the result cell and paste it as a value. That replaces the formula with its current displayed result, so subsequent recalculation will not change that cell.

Can Excel randomly select multiple items without duplicates?
Yes. In Excel 2021 or Microsoft 365, a SORTBY and RANDARRAY formula can shuffle the source rows, and INDEX with SEQUENCE can return the requested number of rows. Duplicate text in the original list can still appear more than once if separate rows contain it.

Why does my random selection sometimes return a blank?
The formula may have selected an empty cell included in its range. Remove blanks from the range or use FILTER to exclude them; make sure at least one nonblank entry remains.

Does Excel’s random selection give every name the same chance?
It gives every cell position the same chance, assuming the list is a fixed range and each position is equally eligible. If one name appears in several cells, that name has a greater overall chance than a name appearing once.

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.