Random Number Generator in Excel: RAND, RANDBETWEEN & More

You opened Excel for a quick task — pick 20 winners from a list, simulate dice rolls, shuffle a roster, maybe stress-test a model with a few thousand fake numbers. The answer almost always starts with one of three functions: RAND, RANDBETWEEN, or the newer RANDARRAY. Each one solves a slightly different problem, and getting them mixed up is how spreadsheets end up with numbers that won't sit still, lists that re-shuffle every time you press Enter, and "random" picks that aren't actually unique.

This guide walks the whole picture. The functions themselves, how to convert volatile randoms into frozen values, how to randomize a list without breaking the data next to it, how to pull a unique random sample, the seeding quirks Excel doesn't really support, and the performance gotchas that hit when you scale past a few hundred thousand rows. By the end you'll know which tool fits each job and how to keep your workbook from recalculating itself into oblivion.

Quick note before we dive in. Excel's randomness is pseudo-random — it comes from a Mersenne Twister algorithm seeded by system state. Good enough for raffles, simulations, sampling, A/B splits, and most business use cases. Not good enough for cryptography. If you're protecting passwords or generating tokens, use a proper CSPRNG outside Excel. For everything else, what's built in is genuinely solid.

One more thing worth saying up front. Randomness in spreadsheets confuses people because the same formula behaves differently depending on context — a single RAND in an empty workbook feels harmless, but a thousand of them in a busy model becomes a recalculation nightmare. Most of what follows is about that gap between "works in isolation" and "works in a real workbook". Once the pattern clicks, it stops being surprising.

The three functions all return random numbers — but they answer different questions. RAND() gives you a decimal between 0 and 1. No arguments. Just type it, press Enter, and Excel hands back something like 0.7384192. Multiply by a range and you scale it. Want a number between 0 and 100? =RAND()*100. Want something between 50 and 75? =RAND()*(75-50)+50. That's the workhorse — flexible, decimal-friendly, and the foundation for almost every other random trick.

RANDBETWEEN(bottom, top) is the integer version. Two arguments, both required. =RANDBETWEEN(1, 100) picks a whole number from 1 to 100 inclusive. This is what you reach for when you need lottery numbers, dice rolls, employee IDs, or anything where decimals would just be noise. The endpoints are included — so RANDBETWEEN(1, 6) can return 1 or 6, not just the numbers in between.

Then there's RANDARRAY — added in Excel 365 and Excel 2021. This one's the big upgrade. A single formula returns a whole array of random numbers. =RANDARRAY(10, 3, 1, 100, TRUE) spills 10 rows × 3 columns of integers from 1 to 100. The fifth argument controls whether you get integers (TRUE) or decimals (FALSE). For anyone doing simulations, sampling, or bulk randomization, RANDARRAY is the one to learn — it's faster, cleaner, and plays nicely with the rest of the dynamic-array stack like SORT, UNIQUE, and FILTER.

Here's the thing about all three functions — they're volatile. Volatile means Excel recalculates them every time anything changes in the workbook. Typed a value in an unrelated cell? Recalc. Pressed F9? Recalc. Saved and reopened? Recalc. Your "winning numbers" from yesterday are different numbers today, and that's almost never what you want.

The fix is to freeze them. Generate your random numbers, select the range, copy (Ctrl+C), then Paste Special > Values (Ctrl+Alt+V, then V, then Enter). That replaces the live formulas with their current results. Now the numbers stay put no matter what else happens in the file. Do this before you sort, filter, or share the workbook — otherwise the ranking flips the moment someone opens it.

The freeze-the-randoms trick is what separates a real workflow from a spreadsheet that argues with itself. You can layer it. Generate. Freeze. Use. If you need a fresh batch, regenerate in a new column and freeze again. The original picks are preserved as an audit trail — which matters if anyone ever asks "how did you pick those names?" months later. A frozen column with a date stamp at the top is a perfectly defensible answer.

For repeatable randomness — say, you want the same shuffle every time you open the file — Excel doesn't expose a seed. That's a real limitation. Workarounds exist (writing the frozen values to a hidden sheet, or driving the randomness from a static index column), but there's no native RAND.SEED(). If reproducibility matters for compliance or testing, freeze the values and document them. Some teams take this further and timestamp the freeze in an adjacent cell so anyone auditing the workbook knows exactly when the picks were made.

Another reason to freeze early — Excel files get shared, and the moment someone else opens your "winners" spreadsheet, a live RAND would recalculate before they even see the original picks. The values they see would not match what you saw. Frozen values eliminate that whole class of confusion. Make freezing a habit, not an afterthought.

One of the most useful applications is shuffling a list — names for a raffle, questions for a quiz, products for an A/B split. The classic method works in any Excel version. Put your list in column A. In column B, fill =RAND() down the same number of rows. Select both columns, then sort by column B ascending. The list reorders randomly. Delete column B if you don't need it.

If you want it cleaner with dynamic arrays, use =SORTBY(A2:A101, RANDARRAY(100)). One formula. The list spills sorted by a fresh batch of random numbers. Refresh by pressing F9 or just typing in any cell. Want it frozen? Copy > Paste Special > Values, same as before.

One subtle issue. RANDBETWEEN with a very wide range — say RANDBETWEEN(1, 1000000000) across thousands of cells — can be slow on older machines and occasionally produce noticeable clustering at the low end on very small samples. For statistical work, prefer =INT(RAND()*N)+1 as the textbook-uniform alternative. For everyday business use, RANDBETWEEN is fine.

And don't forget — random number functions interact with conditional formatting in fun ways. Apply a rule like "highlight if >0.5" to a column of RAND, and every keystroke anywhere in the workbook will repaint that column. Pretty, but expensive.

What if you need unique random picks — say, choose 5 different winners from a list of 200, where nobody gets picked twice? Plain RANDBETWEEN can repeat. You'd type =RANDBETWEEN(1, 200) five times and end up with duplicates more often than people realize. The math isn't on your side.

The bullet-proof method: shuffle the whole list and take the top N. So =INDEX(SORTBY(A2:A201, RANDARRAY(200)), SEQUENCE(5)) hands you 5 unique random picks from a 200-row list. No duplicates possible. If you're stuck on an older Excel without dynamic arrays, the column-B-with-RAND-and-sort trick gives you the same result manually.

RANDARRAY deserves a longer look because it changes how you build models. Old-school Monte Carlo simulations needed hundreds of helper cells, each running RAND or RANDBETWEEN, then a summary table on top. With RANDARRAY you write one formula that spills the whole dataset, then pipe it straight into AVERAGE, STDEV, PERCENTILE, or whatever statistic you need.

Example. You're modeling 10,000 customer arrivals with arrival times distributed between 0 and 8 hours: =RANDARRAY(10000, 1, 0, 8, FALSE). One cell. 10,000 results spill down. Wrap that in =AVERAGE(...) in another cell to get the mean. Press F9 to re-roll. Done. No copy-paste, no fragile cell references, no broken formulas if you insert a row.

If you're new to Excel formulas in general, getting comfortable with how volatile functions behave will save you hours of confusion down the line. The same logic that applies to RAND — recalculation triggers, freezing values, performance — also applies to NOW(), TODAY(), OFFSET, INDIRECT, and a handful of others. Master the pattern once and you'll spot the problem instantly in unrelated workbooks. The skill compounds.

It's also worth knowing when not to use Excel for randomness. Generating cryptographic keys? No. Statistical research where seeds matter? Probably not. Drawing the winning ticket for the office raffle? Absolutely. Excel is the right tool for plenty of jobs — just not every job that involves a random number. For serious statistical work, a Python or R script gives you proper seed control, distribution choices beyond uniform, and reproducibility that Excel simply does not offer. Don't force the wrong tool to do the right job.

For most day-to-day spreadsheet randomness though, you'll never need to leave Excel. The three functions, plus the freezing pattern, plus SORTBY for shuffles, plus the unique-via-shuffle trick — that's a complete toolkit. Anything more advanced is usually a sign you should be using a different tool entirely.

Performance is where it gets interesting. RAND and RANDBETWEEN are volatile per cell. If you have 100,000 RAND cells in a workbook, every single change triggers 100,000 recalculations. That's why big simulation files chug. RANDARRAY is volatile per array — one spilled range counts as one recalculation, not 10,000. The speed difference on large workbooks is enormous, often 10x or more, and it gets more dramatic as the dataset grows.

The other performance trap is helper columns. People build a column of RAND, a column of LOOKUPs against it, another column of conditional logic, and a summary table. Every typed character recalculates the whole chain. Convert the random column to values (freeze it) and the chain stops being volatile. Suddenly your workbook is fast again. This is the single biggest speed-up you can get on a slow Excel file — and most people never try it because they don't realize their helper randoms are the bottleneck.

If you genuinely need live recalculation but the workbook is slow, switch the calculation mode to Manual (Formulas tab > Calculation Options > Manual). Excel won't recalc until you press F9. That lets you keep volatile randoms in the file while still being able to type, scroll, and edit without lag. Press F9 when you actually want a fresh roll. Don't forget to switch back to Automatic for normal use, or save under a different name to avoid confusing your future self.

A few practical patterns worth memorizing. Random decimal between two numbers: =RAND()*(top-bottom)+bottom. Random integer in a range: =RANDBETWEEN(bottom, top). Random date between two dates: =RANDBETWEEN(DATE(2025,1,1), DATE(2025,12,31)) then format the cell as a date. Random selection from a list: =INDEX(list, RANDBETWEEN(1, COUNTA(list))). Random letter A–Z: =CHAR(RANDBETWEEN(65, 90)). Random password chunk of 8 mixed-case letters: =TEXTJOIN("", TRUE, CHAR(RANDARRAY(8, 1, 65, 122, TRUE))) — and yes, you'd want to filter out the non-letter ASCII codes for production, but as a quick starter it works.

A weighted random pick is one more pattern worth knowing. If you want option A to be picked 70% of the time and option B 30%, generate a RAND and check against the threshold: =IF(RAND() < 0.7, "A", "B"). Extend with nested IFs for three or more options. For anything beyond three buckets, switch to a lookup table — put cumulative weights in one column, labels in another, and use VLOOKUP with the approximate-match argument to pick a label from a single RAND. Same idea, much cleaner formula, easier to update when weights change.

One more pattern worth flagging because it trips up so many users — generating random numbers with no duplicates across a giant range. People reach for nested IF statements, complicated array tricks, helper columns full of LOOKUPs. Almost all of it can be replaced with two functions stitched together: UNIQUE wrapped around RANDARRAY. Or more reliably, SORTBY with SEQUENCE as we showed earlier.

The mental shift is this. Don't try to generate random numbers and then deduplicate. Generate a sorted-by-random list and take the first N. That's always unique because the source list is unique. Cleaner, faster, and works the same on 100 rows or 100,000.

To pull it all together — pick the function that matches your output. Decimals between 0 and 1: RAND. Integers in a range: RANDBETWEEN. Whole arrays at once: RANDARRAY. Freeze with Paste Special > Values whenever the result matters. Shuffle lists with SORTBY + RANDARRAY (or RAND + manual sort on older versions). For unique picks, sort the source by random and take the top N. And remember — these are volatile functions. Treat them carefully on large workbooks, and convert to values the moment the randomness has done its job.

The patterns shown here cover roughly 95% of every real-world random-number task in Excel. Raffles, simulations, sampling, shuffles, weighted picks, password chunks, A/B splits, fake test data for dashboards — the same handful of formulas does all of it. The hardest part is usually deciding whether you want volatile or frozen output, and which version of Excel you're targeting. Everything else falls out from those two choices.

With these patterns in your back pocket, almost every random-number question in Excel becomes a one-liner. Spend ten minutes practicing each one on a throwaway file, and you'll never have to look this up again. Browse all Excel quizzes to test the rest of your spreadsheet skills.