Excel Formula for Random Selection From a List: Guide

To return one random item from a contiguous list in Excel, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). If your items are in A2:A20, the formula returns one of those 19 cells; it recalculates, so the selection can change when Excel updates the worksheet.

The basic formula and what it does

The formula combines three functions. INDEX returns a value from a range, RANDBETWEEN generates an integer between two limits, and ROWS counts the cells in the list. Together, they turn a randomly chosen position into the corresponding list item. For example, if the generated position is 7, INDEX returns the seventh value in A2:A20, which is the value in A8.

This approach works in Microsoft 365 and older desktop versions of Excel because INDEX, RANDBETWEEN, and ROWS are long-standing functions. It is a practical choice when you need one result in a cell and the source list has no blank cells mixed in. The first and last positions are included, so every row in the stated range can be selected.

Before using the formula, replace A2:A20 with the actual range. Keep the list reference consistent in both places: the range passed to INDEX and the range counted by ROWS. If you use a larger range than your data, empty cells can be selected too.

Pick one item while ignoring blank cells

A range that contains gaps needs extra care. The basic formula chooses a row position, not a nonblank value. If A2:A20 has empty cells, one of those positions may produce a blank result. In Microsoft 365 or Excel 2021, FILTER can first create a list containing only nonblank entries.

With the items in A2:A20, use this formula to return one nonblank item:

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

LET gives the filtered list the name items, so the formula can use that list both to count the available values and to return the chosen one. If there are no nonblank entries, FILTER has no value to return and Excel displays an error unless you provide an alternative result. For a normal list with at least one entry, the formula selects from the filled cells and skips gaps.

FILTER and LET are available in Microsoft 365 and Excel 2021. In Excel for the web, these functions are also available, though the ribbon and recalculation behavior can differ slightly from the Windows desktop app. If your version does not support them, make a compact list without gaps or use a helper column to identify filled rows.

Choose several items without repeats

For a sample of several different list positions, a random sort is usually clearer than drawing an item repeatedly. SORTBY rearranges the source list according to random sort keys, and SEQUENCE asks INDEX to return the first requested number of rows from that shuffled result.

With 19 nonblank items in A2:A20, enter this formula in an empty cell to return five items:

=INDEX(SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))),SEQUENCE(5))

It spills five results into the cells below the formula. Each source row appears at most once in this sample, and the formula requires at least five source rows. The sample is based on rows: if the original list contains the same name twice, that name can still appear twice because it occupies two separate rows.

SORTBY, RANDARRAY, and SEQUENCE are available in Microsoft 365 and Excel 2021. The cells where results spill must be empty; otherwise Excel shows a spill error. To sample a different number of items, change the 5 in SEQUENCE to the desired count, making sure it does not exceed the number of available rows.

To exclude blank cells as well, use a filtered list before shuffling it. With items in A2:A20 and at least five nonblank entries, enter this in an empty cell:

=LET(items,FILTER(A2:A20,A2:A20<>""),INDEX(SORTBY(items,RANDARRAY(ROWS(items))),SEQUENCE(5)))

This version skips blank cells and returns five list rows without selecting the same row twice. As with the simpler sample, identical values in separate source rows can both appear. If you want unique values rather than unique rows, first remove duplicates or build a distinct list with UNIQUE, which is available in Microsoft 365 and Excel 2021.

Random selection with or without replacement

The basic single-item formula samples with replacement when you copy it into multiple cells. Each formula makes its own random draw, and the same source item may appear more than once. That is useful for simulations or repeated independent picks, but it does not guarantee a set of distinct winners.

A no-repeat sample instead shuffles the available rows and takes the required number from the top, as in the SORTBY example. This is the better model for drawing several different people from a list of entries. Decide which behavior you need before choosing a formula: “random” alone does not imply that duplicates are prohibited.

There is another distinction: duplicate source values affect the odds. If “Jordan” appears in three rows and “Riley” appears in one, a uniform random row draw gives Jordan three chances and Riley one. If each distinct name should have an equal chance, remove duplicate names before selecting. If repeated entries represent valid extra tickets or weighted chances, leave them in.

Use a helper column in older Excel versions

If your Excel version does not have dynamic-array functions such as SORTBY or RANDARRAY, a helper column offers a straightforward way to shuffle a list and take the first few entries. It works in older desktop versions, and it makes the random order visible for review.

With list items in A2:A20, enter a random key beside the first item. In B2, enter =RAND(), then fill the formula down through B20.
Select the full data table, including both columns, and sort by column B. Sorting the whole table keeps each random key attached to its original item.
Take the first five items in column A for a five-row sample without replacement. Recalculate and sort again when you want a new draw.

RAND returns a decimal value from zero up to, but not including, one. Ties are possible in principle, though unlikely for a short list; if two keys tie, their relative order may depend on the sort. Do not sort only the helper column, because that would separate the keys from their corresponding items.

For a single random item in an older version, the basic INDEX and RANDBETWEEN formula is simpler. The helper-column method is most useful when you want multiple selections, need to see the randomized order, or want to save a particular draw by copying the results and pasting values.

Why the selection changes

RAND and RANDBETWEEN are volatile functions: Excel can calculate them again when the workbook recalculates. That means a selected name may change after an edit, a recalculation, or another action that triggers calculation. A random formula is not a permanent record of a draw.

On Windows, F9 recalculates open workbooks, while Shift+F9 recalculates the active worksheet. On a Mac, the function keys may require the Fn key, depending on keyboard settings; Excel’s recalculation shortcuts can also vary with those settings. In Excel for Windows, the calculation setting is under Formulas > Calculation Options. Automatic calculation is the usual setting; choosing Manual can reduce recalculations in a large workbook, but it also means results may not update until you recalculate.

To keep a chosen result, copy the result cell or spilled results and use Paste Special > Values. This replaces the formula with its current displayed value. For a helper-column draw, you can also copy the shuffled table and paste values into a separate area before another recalculation changes its random keys. Keep a copy of the source list if you need an audit trail.

Common problems and how to fix them

The formula returns blank. Check whether the selected range includes empty cells. Use a compact range, or use FILTER to exclude blanks in versions that support it.
The formula returns an error. Confirm that the range contains at least one selectable item and that the requested sample size does not exceed the number of available rows. In a dynamic-array formula, also make sure the spill area is clear.
The same item appears more than once. Multiple separate single-item formulas draw independently and can repeat results. Use a shuffled list for sampling without replacement.
The answer changes unexpectedly. This is normal for volatile random functions. Copy and paste the result as a value when you need to preserve a draw.
The wrong records move during sorting. Select and sort the entire table, not just the random-key column, so that keys remain paired with their list items.

Also check your regional settings if a formula copied from a web page is rejected. Some Excel installations use semicolons rather than commas as argument separators. The examples here use commas, as requested for the standard English-language Microsoft 365 setup.

Frequently Asked Questions

How do I randomly select one name from a list in Excel?
With names in A2:A20 and no blank cells in the range, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). It returns the name at a randomly chosen position and may change when Excel recalculates.

How do I select multiple random items without duplicates?
In Microsoft 365 or Excel 2021, shuffle the list with SORTBY and RANDARRAY, then return the first required number of rows with INDEX and SEQUENCE. For example, the five-item formula above returns five different source rows, provided the list has at least five rows.

Can Excel randomly choose from a list but skip blank cells?
Yes. In Microsoft 365 or Excel 2021, use FILTER to make a nonblank list first, then choose from that filtered result. Alternatively, remove gaps from the source range or use a helper column in older Excel versions.

Why does my random selection keep changing?
RAND and RANDBETWEEN recalculate, so the selected item is not fixed. Copy the result and paste it as a value to preserve the current selection.

Does a random formula give every name an equal chance?
It gives each source row an equal chance when using a uniform random position. If a name appears on several rows, it has more chances than a name appearing once. Remove duplicates first if you want each distinct name to have equal odds.

Which method should you use?

For one item from a clean list, INDEX with RANDBETWEEN is concise and works in nearly every Excel version. For a list with gaps, FILTER plus LET avoids selecting blanks in Microsoft 365 or Excel 2021. For several different items, use SORTBY with RANDARRAY and SEQUENCE in a dynamic-array version, or random helper keys and a full-table sort in older Excel.

Whichever method you choose, be clear about whether repeated source entries represent extra probability, whether blank cells should count, and whether the result must stay fixed. Those choices determine the correct formula and help prevent a plausible-looking random result from being used in the wrong way.

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.