With a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change whenever Excel recalculates, and the same item can be selected again on a later calculation.
The basic random-pick formula
The formula combines three Excel functions: INDEX returns a value from a range, RANDBETWEEN generates a random whole number within a specified range, and ROWS counts the number of rows in the list. In the example, RANDBETWEEN chooses an integer from 1 through 9, and INDEX returns the item at that position in A2:A10.
For example, if A2:A10 contains nine names, the formula might return the name in A6. The next result is not necessarily the next name down: it is another selection from the entire range. Because there are nine entries, each position has the same chance of being selected on each calculation, assuming each row is one eligible entry.
To use it in a worksheet:
- Enter the possible choices in one column, without gaps, such as A2:A10.
- Enter the formula in the cell where you want the selection to appear.
- Recalculate if you want Excel to make a new selection.
You can replace A2:A10 with your actual range. Keep the range references consistent inside the formula: the range used by INDEX should match the range whose row count is measured. The formula works for text, numbers, dates, and other cell values.
How the formula chooses an item
INDEX does not make the choice random by itself. It returns the value at a position you specify. RANDBETWEEN supplies that position, while ROWS makes the upper limit adjust to the size of the selected range. For a list of nine rows, the random position is one of 1, 2, 3, and so on through 9.
This is a position-based selection, not a random search through the contents of the cells. If the same name appears in three different rows, that name has three chances to be returned. If you want every distinct name to have an equal chance regardless of how many times it appears, remove duplicates first or build a separate list of unique choices.
The formula assumes that every row in its range is a valid choice. A blank row still has a position, so it can be selected. Depending on the context, INDEX may display a zero when the selected cell is blank. That is why a compact range with no empty cells is the simplest setup.
Choose randomly from a range with blanks
If the list contains blank cells between entries, first filter out the blanks and then select from the remaining items. With possible entries in A2:A20, and at least one nonblank cell in that range, use this formula in Microsoft 365 or Excel 2021:
With choices in A2:A20 and possible blank cells anywhere in that range, use =LET(items,FILTER(A2:A20,A2:A20<>""),INDEX(items,RANDBETWEEN(1,ROWS(items)))).
FILTER creates a temporary list containing only nonblank cells. LET gives that filtered list the name items, so the same result can be used both as the range for INDEX and to count its rows. The formula then chooses a position from that shorter list. It returns an error if every cell is blank, so make sure at least one eligible value exists.
For a list that is always filled from the top with no gaps, the basic formula is easier to maintain. A shortcut such as using COUNTA to count the entries is safe only when the nonblank entries occupy the first cells of the range without gaps. For example, if A2:A20 has values in A2:A5 but blanks below, a random position from 1 to 4 correctly addresses those values; if A3 is blank but A4 contains a value, counting nonblank cells does not identify the correct row positions.
Pick several items without repeating a row
Copying the single-pick formula down several cells does not guarantee different choices. Each formula makes its own random selection from the full list, so repeats are possible. If you need a random ordering of the whole list, a dynamic-array formula can shuffle the rows instead.
With the source list in A2:A10, use =SORTBY(A2:A10,RANDARRAY(ROWS(A2:A10))) in Microsoft 365 or Excel 2021. Enter it in an empty cell with enough room below for the results to spill. SORTBY orders the source values according to a newly generated random number for each row, producing a randomized version of the list. Use the first several spilled rows if you need a sample without returning the same source row twice.
This method does not remove duplicate values already present in the source. If A2 and A5 both contain the same product name, both rows remain in the shuffled output, even though each individual row appears only once. Also check that the spill area is empty: an occupied cell below the formula can prevent the results from appearing and produce a spill error.
For older Excel versions without RANDARRAY and SORTBY, put =RAND() in B2 when the source entries are in A2:A10, fill it down through B10, and sort the complete table by column B. RAND assigns a random decimal to each row. To keep those random keys from changing during the sort, copy B2:B10 and use Paste Special > Values before sorting. Sort the source and helper values together, not the helper column alone, or the choices will no longer match their random keys.
Keep a selection from changing
RANDBETWEEN is a volatile function: Excel recalculates it when calculation occurs, so the chosen item can change after you edit cells, open or recalculate the workbook, or trigger another calculation. This is useful when you want a fresh draw, but inconvenient when you need to record one stable choice.
Once the result is the one you want, copy the result cell and paste only its value back into the same cell or another cell. The displayed choice then becomes a fixed value rather than a live formula. This is useful for a draw, a classroom activity, or a one-time assignment where you need to preserve the outcome. Keep a copy of the original formula elsewhere if you may want to generate another selection later.
To request a new draw on Windows, press F9 to recalculate. Shift+F9 recalculates the active worksheet. On a Mac, the function-key behavior can depend on keyboard settings; using Formulas > Calculate Now is an alternative. Excel for the web uses the same formula syntax, though keyboard shortcuts and recalculation controls can differ from the desktop app.
Common problems and checks
- The result changes unexpectedly: that is normal for a live random formula. Copy and paste the result as a value if it must stay fixed.
- A blank appears: check for empty cells in the source range. Use a compact list or the filtered formula above.
- The same choice appears more than once: separate single-pick formulas allow repeats. Shuffle the full list and use its first rows when sampling without replacement.
- The formula shows an error: confirm that the range contains at least one eligible item and that the formula uses the expected comma argument separators. In regional settings that use semicolons, Excel may display or require semicolons instead.
- The formula will not spill: clear cells below the SORTBY formula so the shuffled results have room to appear.
Excel normally uses automatic calculation, but a workbook can be set to manual calculation. If a random result is not updating when expected, check Formulas > Calculation Options. Setting calculation to Automatic allows formulas to recalculate as workbook changes occur; pressing F9 can also prompt a calculation in desktop Excel.
Which approach should you use?
For one quick selection from a short, filled list, INDEX with RANDBETWEEN is usually the most straightforward option and works in older versions of Excel as well as current releases. If the range has blanks, filter the eligible values before selecting. If you need several different rows, shuffle the list rather than copying the single-choice formula and assuming it will avoid duplicates.
Think about what counts as one choice before building the sheet. If repeated entries represent extra chances in a weighted drawing, leave them in the range. If each distinct option should have one equal chance, remove duplicates first. If a name should have twice the chance of another, include it twice in the source list or use a more explicit weighting method. The formula treats rows equally; it does not infer a weighting rule from labels or categories.
Also decide whether the outcome is temporary or part of a record. A live formula is convenient for repeated random draws, but it is not a permanent audit trail. For a result that must remain available later, paste it as a value and, where appropriate, record the date or other context separately.
Frequently Asked Questions
How do I randomly select one cell from a column in Excel?
Put the choices in a continuous range such as A2:A10, then enter =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) in another cell. Adjust both range references to match your list.
Can Excel pick a random name without repeating?
Yes. In Microsoft 365 or Excel 2021, shuffle the list with SORTBY and RANDARRAY, then take the required number of entries from the top of the spilled results. Repeated values in different source rows can still appear as duplicates by name.
Why does my random Excel formula keep changing?
RANDBETWEEN recalculates when Excel recalculates the workbook or worksheet. To preserve the current selection, copy the cell and paste it as a value.
How can I stop Excel from choosing a blank cell?
Remove gaps from the source range, or use FILTER to create a temporary list of nonblank choices before selecting. Check that the filtered range contains at least one value.
Does the random-pick formula work in older Excel versions?
The INDEX, RANDBETWEEN, and ROWS formula works in many older Excel versions. The blank-filtering and shuffle examples use newer dynamic-array functions, available in Microsoft 365 and Excel 2021 or later.
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
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
- Count Excel Without Duplicates: Formulas That WorkTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- Excel Text Plus Cell Value: Formulas, Formatting, and FixesTo combine text with a cell value in Excel, join them with the ampersand operator (&). For example,…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
- Excel IFS Function Examples for Multiple ConditionsExcel’s IFS function checks conditions in order and returns the value paired with the first condition that is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.