How to Create a Random Number Generator in Excel

Excel has two built-in functions that generate random numbers on the fly — and knowing which one to use, and how to control the output, makes a significant difference depending on what you're actually trying to do. Whether you're running simulations, assigning random IDs, building test datasets, or just picking a winner from a list, Excel's randomization tools are more capable than most users realize.

The Two Core Functions: RAND vs RANDBETWEEN

RAND() generates a decimal number between 0 and 1 — for example, 0.472839. It takes no arguments. Just type =RAND() into any cell and hit Enter.

RANDBETWEEN(bottom, top) generates a whole number between two values you specify. For example, =RANDBETWEEN(1, 100) returns a random integer anywhere from 1 to 100. This is the function most people actually want when they picture a "random number generator."

Both functions are volatile — meaning they recalculate every time anything in the spreadsheet changes. If you type in a different cell, your random numbers change. This is important to understand before you build anything that depends on stable values.

How to Generate a Basic Random Number 🎲

Step 1: Click on any empty cell.

Step 2: Type =RANDBETWEEN(1, 100) and press Enter.

You'll see a random whole number between 1 and 100. To generate a new one, press F9 — this forces Excel to recalculate.

To fill a column with random numbers, click the cell, then drag the fill handle (the small square at the bottom-right of the cell) down as many rows as you need.

Locking Random Numbers So They Don't Change

Because these functions are volatile, the values will shift constantly during normal spreadsheet use. If you need to freeze the results:

1. Select the cells containing your random numbers
2. Copy them (Ctrl+C)
3. Right-click → Paste Special → Values

This replaces the formulas with their current output as static numbers. From this point, they won't change regardless of what else happens in the spreadsheet.

This step is critical for any real-world use case where consistency matters — random IDs, test data samples, raffle selections, and similar applications all require frozen values.

Generating Random Numbers Within a Specific Range

If you need decimals within a custom range rather than 0–1, you can scale RAND() manually:

=RAND() * (max - min) + min

For example, to get a random decimal between 5 and 10:

=RAND() * (10 - 5) + 5

This is useful in statistical modeling, Monte Carlo simulations, or anywhere you need continuous rather than discrete random values.

Generating a List Without Duplicates

RANDBETWEEN can (and frequently does) produce duplicate values when used across multiple cells. If you need a set of unique random numbers — say, for a lottery draw or random ordering — the approach requires a workaround.

Common method:

1. In column A, list your items or a number sequence (1 through 50, for example)
2. In column B next to each item, enter =RAND()
3. Sort both columns by column B
4. The result is a randomly shuffled, duplicate-free order

This technique works because you're using RAND() as a sort key rather than a direct number generator. Each value in column B is unique (the probability of exact duplicates with RAND() is effectively zero), so the sort produces a clean random permutation.

Using RANDARRAY in Excel 365 and Excel 2021

Newer versions of Excel introduced RANDARRAY, a dynamic array function that can generate a full grid of random numbers in one formula:

=RANDARRAY(rows, columns, min, max, integer)

For example, =RANDARRAY(5, 3, 1, 100, TRUE) fills a 5-row by 3-column range with random integers between 1 and 100 — all from a single cell entry.

This is significantly more efficient when working with large datasets or building dashboards, but it's only available in Microsoft 365, Excel 2021, and the web version. Older versions like Excel 2016 or 2019 don't include it. 📊

Variables That Affect Which Approach Works for You

The right method depends on several factors that vary by user:

• Excel version — RANDARRAY isn't available in older installations, which limits your options without workarounds
• Output type — whether you need integers, decimals, or a shuffled list changes which function is appropriate
• Volatility tolerance — if your sheet is large or complex, recalculation on every change can slow things down noticeably
• Duplicate tolerance — simulations may accept duplicates; draws or ID assignments almost never can
• Skill level — the paste-special freeze step or RANDARRAY syntax can trip up less experienced users if they're not expecting volatile behavior

A straightforward raffle in a small spreadsheet is a very different problem from building a simulation with 10,000 randomized data points, even though both technically use "random number generation." The functions are the same — but the surrounding structure, freezing strategy, and formula complexity diverge considerably once you factor in scale and purpose.

Whether the basic RANDBETWEEN approach fully covers your needs, or whether you're looking at RANDARRAY, custom formulas, or even VBA-based solutions, comes down to the specifics of your spreadsheet and what you're actually trying to produce with those numbers.