How to Generate a Random Number in Google Sheets
Use =RANDBETWEEN(1,100) for a random whole number between 1 and 100. For a decimal between 0 and 1, use RAND.
=RANDBETWEEN(1,100)
Returns a random whole number from 1 to 100 inclusive. Change the bounds to your range.
How it works
RANDBETWEEN returns a random integer between the two bounds you give, inclusive. RAND returns a random decimal between 0 and 1, which you can scale to any range. Both are volatile — they recalculate on every edit — so to keep a value fixed, copy the cell and Paste special → Values only.
Variations
Random decimal between 0 and 1
=RAND()
Multiply to scale, e.g. =RAND()*50 for 0 to 50.
Random decimal in a range
=RAND()*(100-1)+1
Gives a decimal between 1 and 100 rather than a whole number.
Pick a random item from a list
=INDEX(A2:A20,RANDBETWEEN(1,COUNTA(A2:A20)))
Chooses a random row from the list in A2:A20.
Examples
| Scenario | Formula |
|---|---|
| Random dice roll | =RANDBETWEEN(1,6) |
| Random winner from a name list | =INDEX(A2:A20,RANDBETWEEN(1,COUNTA(A2:A20))) |
FAQ
Why does my random number keep changing?
RAND and RANDBETWEEN are volatile and recalculate on every edit. Copy the cell and Paste special → Values only to freeze it.
How do I get a random decimal instead of a whole number?
Use =RAND() for 0-1, or =RAND()*(high-low)+low for a custom range.
How do I pick a random name from a list?
Use =INDEX(range,RANDBETWEEN(1,COUNTA(range))).
Related formulas
- How to VLOOKUP from Another Sheet in Google Sheets
- COUNTIF vs COUNTIFS in Google Sheets — When to Use Each
- FILTER vs QUERY in Google Sheets — Which Should You Use
- IF vs IFS in Google Sheets — When to Use Each
- INDEX MATCH vs VLOOKUP in Google Sheets — Which Is Better
- SUMIF vs SUMIFS in Google Sheets — When to Use Each
- TEXTJOIN vs CONCATENATE in Google Sheets — Which to Use
- UNIQUE vs Remove Duplicates in Google Sheets — Which to Use