The simplest way to generate random numbers in Excel
Excel has a built-in function called RAND() that produces a random decimal number between 0 and 1 each time you use it. Type =RAND() into any cell and press Enter. The cell will show a number like 0.547392 or 0.891204. Every time Excel recalculates the sheet — when you press F9, change another cell, or reopen the file — RAND() produces a new random number.
If you need whole numbers instead of decimals, wrap RAND() inside the RANDBETWEEN() function. Type =RANDBETWEEN(1,100) to get a random integer between 1 and 100. Change the numbers to whatever range you want: =RANDBETWEEN(1,6) for a dice roll, or =RANDBETWEEN(1,52) for a card deck simulation.
Both functions recalculate automatically whenever your spreadsheet updates. If you want a random number that stays the same and does not change, copy the cell with the formula, then paste it as a value using Paste Special (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac). Choose "Values only" to lock the number in place.
Key Takeaways
- RAND() generates a random decimal between 0 and 1, and RANDBETWEEN() generates a random whole number within a range you specify.
- Both functions recalculate every time Excel updates, so the numbers change unless you convert them to fixed values.
- RANDBETWEEN(1,100) is the easiest way to simulate dice rolls, lottery picks, or any random selection from a range.
- You can copy random numbers and paste them as values to freeze them in place so they stop changing.
- Combining random numbers with other functions like INDEX or CHOOSE lets you randomly pick items from a list.
Using RANDBETWEEN for whole numbers and specific ranges
RANDBETWEEN is more practical than RAND for most real work because you usually want whole numbers, not decimals. The syntax is =RANDBETWEEN(bottom, top). Both the bottom and top numbers are included in the possible results, so =RANDBETWEEN(1,10) can produce 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10.
Common uses: =RANDBETWEEN(1,6) for rolling a single die, =RANDBETWEEN(1,52) for drawing a card, =RANDBETWEEN(1,365) for a random day of the year. You can also use negative numbers: =RANDBETWEEN(-10,10) produces numbers from -10 to 10.
If you need multiple random numbers in a column, type the formula in the first cell, then copy it down. Click the cell with the formula, grab the small square at the bottom-right corner of the cell, and drag down as far as you need. Each row will get its own independent random number.
Randomly selecting items from a list
Combine RANDBETWEEN with INDEX to pick a random item from a list. Say you have names in cells A1 through A10. Type =INDEX(A1:A10,RANDBETWEEN(1,10)) to randomly select one name. The RANDBETWEEN picks a random position (1 through 10), and INDEX retrieves the name at that position.
You can also use CHOOSE instead of INDEX for shorter lists. =CHOOSE(RANDBETWEEN(1,3),"Red","Blue","Green") randomly picks one of those three colors. CHOOSE works best when you have fewer than 10 options; for longer lists, INDEX with a range is cleaner.
If your list has gaps or is not in a single column, INDEX still works — just make sure the range covers all the cells you want to pick from, even if some are empty. RANDBETWEEN will occasionally land on an empty cell, so this method works best with complete, unbroken lists.
Freezing random numbers so they stop changing
By default, RAND() and RANDBETWEEN() recalculate constantly. If you want a random number that stays the same, you must convert the formula to a value. Click the cell with the random number, copy it (Ctrl+C), then right-click and choose "Paste Special". Select "Values" and click OK. The formula disappears and the number locks in place.
You can also use the keyboard shortcut: copy the cell, then press Ctrl+Shift+V (Windows) or Cmd+Shift+V (Mac) to open Paste Special directly. Choose "Values only" and the number becomes permanent.
If you have many random numbers to freeze, select all of them at once, copy, then paste as values. This is faster than doing them one at a time. After pasting as values, you can delete the original formulas if you no longer need them.
Controlling when random numbers recalculate
Excel recalculates RAND() and RANDBETWEEN() whenever you change anything in the sheet. If this is annoying — for example, if you are building a model and the random numbers keep shifting — you can change the calculation mode. Go to the Formulas tab (or Formulas menu on Mac) and look for "Calculation Options". Choose "Manual" instead of "Automatic".
In Manual mode, random numbers only recalculate when you press F9 or Ctrl+Shift+F9. This gives you control: you can make changes to your spreadsheet without the random numbers jumping around, then press F9 once when you are ready to generate new numbers.
Be careful: Manual calculation mode affects the entire workbook, not just one sheet. If you switch back to Automatic, all your formulas recalculate again. For most users, leaving it on Automatic is simpler — just be aware that random numbers will change as you work.
Creating a weighted random selection
Sometimes you want certain outcomes to be more likely than others. For example, you might want a 70% chance of "Yes" and a 30% chance of "No". Use =IF(RAND()<0.7,"Yes","No"). Since RAND() produces a number between 0 and 1, this formula returns "Yes" roughly 70% of the time and "No" roughly 30% of the time.
You can extend this to more than two options using nested IF statements: =IF(RAND()<0.5,"A",IF(RAND()<0.67,"B","C")) gives roughly 50% A, 33% B, and 17% C. The logic gets harder to follow with many options, so this method works best for two or three weighted choices.
For more complex weighting — say, picking from a list where each item has a different probability — use a lookup table with cumulative percentages and VLOOKUP. This is more advanced, but it lets you assign any probability you want to any outcome.
Avoiding common mistakes with random number formulas
The most common error is forgetting the equals sign. RAND() without the = at the start is just text, not a formula. Always start with =RAND() or =RANDBETWEEN().
Another mistake is using RANDBETWEEN with the wrong order. =RANDBETWEEN(100,1) will not work — the bottom number must be smaller than the top number. If you need a range that includes negative numbers, make sure the negative number comes first: =RANDBETWEEN(-50,50).
If your random numbers keep changing and you do not want them to, you probably forgot to paste as values. Remember: formulas always recalculate. To lock a number in place, paste it as a value. If you accidentally paste as values and later want the formula back, you will have to retype it — there is no undo for the formula itself once it becomes a number.
Frequently Asked Questions
Can I generate random numbers without them changing every time I open the file?
Yes. Copy the cells with random formulas, then paste them as values using Paste Special. This converts the formulas to fixed numbers that never change. You can also set calculation mode to Manual so numbers only recalculate when you press F9.
What is the difference between RAND and RANDBETWEEN?
RAND() produces a decimal between 0 and 1, like 0.547. RANDBETWEEN() produces a whole number within a range you specify, like a number between 1 and 100. Use RANDBETWEEN for most practical tasks because whole numbers are easier to work with.
How do I randomly pick a name from a list?
Use INDEX with RANDBETWEEN: =INDEX(A1:A10,RANDBETWEEN(1,10)) if your names are in cells A1 through A10. This picks a random position in the list and returns the name at that position.
Why do my random numbers keep changing when I do not want them to?
Formulas recalculate automatically. To stop this, copy the cells with random numbers and paste them as values. Right-click, choose Paste Special, select Values, and click OK. The numbers will then stay the same.
Can I make some random outcomes more likely than others?
Yes, using IF with RAND(). For example, =IF(RAND()<0.7,"Yes","No") returns "Yes" about 70% of the time and "No" about 30% of the time. Adjust the decimal (0.7) to change the probability.