SELECT_RANDOM_CELL
Select a random non-empty value from a range for sampling or selection.
=SELECT_RANDOM_CELL(range)Gibt zurück: any
Übersicht
SELECT_RANDOM_CELL picks a random value from all non-empty cells in a given range. Each time the spreadsheet recalculates, a new random selection is made from the available values. Empty cells are automatically excluded, so you always get a meaningful value.
Parameter
| Parameter | Typ | Erforderlich | Beschreibung |
|---|---|---|---|
range | Range | Ja | The range containing values to randomly select from. |
Beispiele
Pick a random team member for code review
Randomly assign a code reviewer from the list of developers.
=SELECT_RANDOM_CELL(A2:A8)Eingabe
| Alice |
| Bob |
| Carol |
| Dave |
| Eve |
Ausgabe
CarolRandom product for quality inspection
Select a random product from the batch list for quality control sampling.
=SELECT_RANDOM_CELL(B2:B100)Eingabe
| SKU-001 |
| SKU-042 |
| SKU-107 |
| SKU-203 |
Ausgabe
SKU-107Random quiz question selection
Pick a random question from a question bank for a pop quiz.
=SELECT_RANDOM_CELL(C2:C50)Eingabe
| What is 2+2? |
| Name the capital of France |
| What year was Google founded? |
Ausgabe
Name the capital of FranceRandom motivational quote
Display a random motivational quote from a curated list each time the sheet refreshes.
=SELECT_RANDOM_CELL(A1:A20)Eingabe
| Stay hungry, stay foolish |
| The best way to predict the future is to create it |
| Done is better than perfect |
Ausgabe
Done is better than perfectAnwendungsfälle
Random sampling for audits
Select random items from a production batch or transaction list for compliance auditing and quality control.
Raffle and giveaway draws
Pick random winners from an attendee or subscriber list for giveaways, raffles, or promotional contests.
Randomized task assignment
Randomly assign tasks, reviews, or on-call duties from a pool of available team members.
A/B test variant selection
Randomly assign test subjects to variant groups by selecting from a range of group labels.
Classroom engagement
Randomly select a student to answer a question, present, or lead a group activity to ensure equal participation.
Profi-Tipps
To freeze a random selection, copy the cell and use Paste Special > Values Only (Ctrl+Shift+V).
Place multiple SELECT_RANDOM_CELL formulas in a row to generate a random sample of N items (though duplicates are possible).
Use on a filtered or curated list to ensure all candidates meet your criteria before random selection.
Combine with a button (via Apps Script) to trigger recalculation on demand for a "spin the wheel" experience.
For weighted random selection, repeat values in the source range proportionally to their desired probability.
This function is useful in many scenarios: random sampling for quality checks, picking lottery or raffle winners, selecting random items for review, generating random test data from a pool of options, and creating randomized quizzes or exercises. The randomness is based on JavaScript's Math.random(), which provides uniform distribution across all non-empty values in the range.
Because Google Sheets recalculates on every edit, the random selection changes each time you modify any cell in the sheet. If you need to "freeze" a random selection, copy the result cell and paste as value only (Ctrl+Shift+V). This converts the dynamic formula result to a static value that will not change.
The function works with any range shape and any data type. It handles ranges of any size, from a few cells to thousands of entries. For accessing specific positions from the end of a range instead of random selection, see RETURN_LAST_CELL and RETURN_CELL_INVERSED.
Häufige Fehler
Same value returned repeatedlyUrsache: The range has very few non-empty cells, or the spreadsheet is not recalculating.
Lösung: Verify the range contains multiple values. Force recalculation by editing any cell. With few values, repetition is expected statistically.
Returns empty stringUrsache: The range is empty or all cells contain only whitespace.
Lösung: Check that the range reference is correct and contains actual data. Cells with only spaces are treated as non-empty but may appear blank.
Häufig Gestellte Fragen
Yes, the value changes each time the spreadsheet recalculates, which happens on every cell edit. To keep a result, copy the cell and Paste Special > Values Only (Ctrl+Shift+V).
No, empty cells are filtered out before selection. Only non-empty cells are candidates for random selection.
The function uses JavaScript Math.random() which provides a uniform pseudo-random distribution. Each non-empty cell has an equal probability of being selected.
Each SELECT_RANDOM_CELL call returns one value. To pick multiple, use the function in multiple cells. Note that duplicates are possible since each call is independent.
Select the cell with the formula, copy it (Ctrl+C), then Paste Special > Values Only (Ctrl+Shift+V). This replaces the formula with its current result.
Yes, if there is only one non-empty cell, that value is always returned.
Not directly, since each call is independent. For sampling without replacement, you would need to combine with SORT by RAND or use a helper column approach.
The function returns an empty string when no non-empty cells are found in the range.
Verwandte Funktionen
Beginnen Sie noch heute mit SELECT_RANDOM_CELL
Installieren Sie Unlimited Sheets, um SELECT_RANDOM_CELL und 41 weitere leistungsstarke Funktionen in Google Sheets zu erhalten.
