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. To use it, click on any empty cell, type =RAND(), and press Enter. The cell will display a number like 0.472 or 0.891. Every time Excel recalculates — which happens when you change any cell or reopen the file — RAND() produces a new random number.
If you need whole numbers instead of decimals, use RANDBETWEEN(). This function lets you set a range. For example, =RANDBETWEEN(1,100) generates a random whole number between 1 and 100. Type the formula, press Enter, and you get one result. Like RAND(), it changes every time the sheet recalculates.
Both functions work the same way: they live in a cell and update automatically. This is useful for simulations, games, or testing, but it can be frustrating if you want numbers that stay put. We'll cover how to lock them in place later.
Key Takeaways
- RAND() produces decimals between 0 and 1, while RANDBETWEEN() produces whole numbers within a range you set.
- Both functions recalculate every time the spreadsheet changes, so your random numbers will shift unless you convert them to fixed values.
- To keep random numbers from changing, copy the cells and paste them as values only using Paste Special.
- You can combine RANDBETWEEN() with other formulas to create weighted results or shuffle lists.
When to use RAND() versus RANDBETWEEN()
Use RAND() when you need a decimal probability or a number to scale against something else. For instance, if you're simulating a coin flip, you might say "if RAND() is less than 0.5, it's heads." RAND() is also the foundation for more complex random operations because you can multiply or divide it to fit any range.
Use RANDBETWEEN() when you want a whole number within a specific range and don't want to do math. It's faster to type =RANDBETWEEN(1,52) for a card deck than to build a formula around RAND(). RANDBETWEEN() is also clearer to read if someone else opens your spreadsheet later.
Both functions recalculate constantly, which is their main limitation. If you need numbers that stay the same, you'll need to convert them — see the next section.
How to stop random numbers from changing
By default, RAND() and RANDBETWEEN() update every time you edit the spreadsheet. To lock them in place, you must convert the formula to its result. Here's how:
- Create your random numbers using RAND() or RANDBETWEEN() in a column or range.
- Select all the cells containing the formulas.
- Press Ctrl+C (or Cmd+C on Mac) to copy.
- Right-click on the same cells and choose Paste Special.
- In the dialog box, click Values only, then click OK.
The formulas are now replaced with their current numbers. They will never change again, even if you edit other cells or reopen the file. This is the only way to keep random results permanent.
If you want some cells to update and others to stay fixed, put the ones you want to keep in a separate column before converting them. That way you can regenerate the others without affecting your locked values.
Building a random number generator for specific tasks
A straightforward random number is often just the start. You might need to shuffle a list, weight results toward certain numbers, or generate multiple independent sets. Here are three common setups.
Shuffling a list: If you have names or items in column A and want to randomize their order, add RAND() in column B next to each item. Then select both columns, sort by column B, and delete column B. The list is now shuffled. This works because sorting by random numbers scrambles the original order.
Weighted random results: If you want some outcomes to be more likely than others, use nested IF statements with RAND(). For example, =IF(RAND()<0.7,"Common",IF(RAND()<0.9,"Rare","Legendary")) gives a 70% chance of "Common", 20% of "Rare", and 10% of "Legendary". This is useful for games or simulations where probabilities matter.
Multiple independent generators: If you need several random numbers that don't affect each other, put each formula in its own cell. They will all recalculate independently. If you want them to stay independent but fixed, convert each group to values at different times so they don't all lock to the same moment.
Common mistakes and how to avoid them
The most common mistake is forgetting that RAND() and RANDBETWEEN() recalculate constantly. You generate a number, look away, and when you look back it has changed. The fix is to convert to values as soon as you have a number you want to keep.
Another mistake is using RANDBETWEEN() with the wrong range. =RANDBETWEEN(1,10) includes both 1 and 10, so you get 10 possible results, not 9. If you want 0 through 9, use =RANDBETWEEN(0,9). Count your endpoints carefully.
A third mistake is putting RAND() or RANDBETWEEN() in a formula that references itself. For example, if cell A1 contains =RANDBETWEEN(1,10) and cell B1 contains =A1+RANDBETWEEN(1,10), both will recalculate every time the sheet updates, making it hard to track what changed. Keep random generators in their own cells separate from calculations that use them.
When a random number generator is the wrong tool
Excel's random functions work well for small simulations, shuffling, and testing. But if you need truly random data for statistics, cryptography, or large-scale modeling, Excel is not the right choice. Excel's random functions are pseudo-random, meaning they follow a pattern that repeats — they're random enough for games and shuffles, but not for security or rigorous science.
If you're generating thousands of random numbers for a statistical model, a programming language like Python or R will be faster and more reliable. If you need to shuffle a large dataset repeatedly, a database tool will handle it better than copying and pasting formulas. Excel is best for quick, small-scale randomization where speed and simplicity matter more than mathematical rigor.
Frequently Asked Questions
Why do my random numbers keep changing when I don't want them to?
RAND() and RANDBETWEEN() recalculate every time Excel updates, which happens when you edit any cell or reopen the file. To stop this, copy the cells with the formulas, right-click, choose Paste Special, select Values, and click OK. This replaces the formulas with their current numbers, which never change.
Can I generate random numbers without decimals?
Yes. Use RANDBETWEEN() instead of RAND(). Type =RANDBETWEEN(1,100) to get a whole number between 1 and 100. RAND() always produces decimals, but RANDBETWEEN() always produces whole numbers within the range you set.
How do I make sure each random number is different?
Excel's RAND() and RANDBETWEEN() do not may provide uniqueness — the same number can appear twice. To shuffle a list so each item appears once, add RAND() next to each item, sort by the random column, then delete the random column. For a smaller list, you can also manually check for duplicates and regenerate any that repeat.
Can I set a seed so the same random numbers appear every time?
Excel does not have a built-in seed function like some programming languages do. If you need reproducible random numbers, you'll need to generate them once, convert them to values, and save the file. Or use a programming tool that supports seeds, which gives you the same sequence every time you run it.
What's the difference between RAND() and RANDBETWEEN()?
RAND() produces a decimal between 0 and 1 with many decimal places. RANDBETWEEN() produces a whole number within a range you specify, like 1 to 100. Use RAND() for probabilities or scaling; use RANDBETWEEN() when you need a straightforward whole number in a specific range.