How to Randomly Generate Numbers in Excel

Excel can generate random numbers for test data, raffles, random sampling, simulations or shuffling a list. Three functions do almost all the work: RAND for decimals between 0 and 1, RANDBETWEEN for whole numbers in a range, and RANDARRAY for filling a whole block of cells at once in Excel for Microsoft 365 and Excel 2021 or later.

This guide shows how to use each one, how to stop the numbers from changing, and how to get random numbers without duplicates.

Which Random Function Should You Use?

Function	What it returns	Example
RAND	A decimal from 0 up to (but not including) 1	=RAND()
RANDBETWEEN	A whole number between two values, inclusive	=RANDBETWEEN(1,100)
RANDARRAY	A spilled range of random decimals or whole numbers	=RANDARRAY(10,1,1,100,TRUE)

How to Generate Random Numbers in Excel

Step 1: Generate a random decimal with RAND

Click a cell, type =RAND() and press Enter. You’ll get a value such as 0.4721. To get a decimal in a different range, scale it: =RAND()*(50-10)+10 returns a decimal between 10 and 50.

Step 2: Generate a whole number with RANDBETWEEN

Type =RANDBETWEEN(1,100) to get a whole number from 1 to 100, including both ends. Negative numbers work too, for example =RANDBETWEEN(-10,10).

Step 3: Fill more cells

Drag the fill handle down or across to copy the formula. Each cell gets its own random value. In Microsoft 365, you can skip copying and use one formula instead: =RANDARRAY(20,3,1,500,TRUE) fills 20 rows and 3 columns with whole numbers from 1 to 500. Set the last argument to FALSE for decimals. Microsoft documents every argument on its RANDARRAY function page.

Step 4: Recalculate when you want new numbers

Random functions recalculate whenever the sheet changes. Press F9 to generate a fresh set on demand.

Step 5: Lock the results

To keep the numbers from changing, select them, press Ctrl + C, then right-click and choose Paste Options > Values. For a single cell, click into the formula bar, press F9 to turn the formula into its result, and press Enter.

Random Numbers Without Duplicates

RANDBETWEEN can repeat values. To get unique random numbers in Excel for Microsoft 365, shuffle a sequence instead:

• =SORTBY(SEQUENCE(10),RANDARRAY(10)) returns the numbers 1 to 10 in random order.
• =TAKE(SORTBY(SEQUENCE(100),RANDARRAY(100)),5) picks 5 unique numbers from 1 to 100, which is handy for a draw.

In older versions, put =RAND() next to a list of numbers 1 to 10, then use =RANK(B2,$B$2:$B$11) in the next column. Each rank is unique, so the result is a shuffled list without repeats.

Other Useful Random Formulas

• Random date: =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)), then format the cell as a date.
• Random item from a list: =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). For more on this, see how to pull a random sample in Excel.
• Random letter: =CHAR(RANDBETWEEN(65,90)) returns a capital letter from A to Z.
• Random decimal with set places: =ROUND(RAND()*100,2) returns a value from 0 to 100 with two decimals.
• Normally distributed values: =NORM.INV(RAND(),50,10) returns values centered on 50 with a standard deviation of 10, useful for realistic test data.
• Random TRUE/FALSE: =RAND()<0.5 returns TRUE about half the time.

Practical Examples

Shuffle a list of names

With names in A2:A21, =SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21))) returns the whole list in random order. Press F9 to reshuffle. This works well for presentation order, seating charts or Secret Santa draws.

Pick a raffle winner

Number your entries in column A and put names in column B. Then use =INDEX(B2:B200,RANDBETWEEN(1,COUNTA(B2:B200))) to pick one name at random. Paste the result as a value before announcing it, so it doesn’t change when someone edits the sheet.

Split people into random teams

Add =RAND() next to each name, sort the list by that column, and then assign team numbers down the list with =MOD(ROW()-2,4)+1 for four teams. Every sort gives a new random split.

Create test data quickly

Combine functions to fill a mock sales table: random dates in one column, =RANDBETWEEN(1,50) for quantities, and =ROUND(RAND()*200+20,2) for prices. Paste everything as values once the table looks right, so your test results stay consistent.

Use the Analysis ToolPak Random Number Generator

For large data sets or specific distributions, enable the Analysis ToolPak under File > Options > Add-ins > Manage Excel Add-ins > Go. Then go to Data > Data Analysis > Random Number Generation. You can choose Uniform, Normal, Bernoulli, Binomial, Poisson and other distributions, and enter a Random Seed so you get the same set again later. The output is static values, not formulas.

Troubleshooting Random Numbers

• The numbers keep changing. That’s by design. Paste them as values once you’re happy with them.
• The numbers never change. Calculation is set to Manual. Press F9, or go to Formulas > Calculation Options > Automatic.
• #NAME? error. RANDARRAY, SEQUENCE, SORTBY and TAKE need Excel for Microsoft 365, Excel 2021 or later (TAKE needs Microsoft 365 or Excel 2024).
• #SPILL! error. A RANDARRAY formula needs empty cells to spill into. Clear the cells below or beside it.
• #NUM! from RANDBETWEEN. The bottom value is larger than the top value. Swap them.
• Duplicates appear. RAND and RANDBETWEEN don’t prevent repeats. Use the SORTBY and SEQUENCE method above.

Frequently Asked Questions

Are Excel’s random numbers truly random?

They’re pseudo-random, which is fine for sampling, games and test data. They aren’t suitable for security uses like passwords or encryption keys.

How do I stop random numbers from changing?

Copy the cells and paste them as values, or press F9 in the formula bar for a single cell.

Can I make random numbers repeatable?

Not with RAND or RANDBETWEEN. Use the Analysis ToolPak’s Random Number Generation tool with a fixed seed instead.

Do these functions work in Google Sheets?

Yes. RAND, RANDBETWEEN, RANDARRAY, SEQUENCE and SORT all work in Google Sheets, with small syntax differences for RANDARRAY.

Wrapping Up

Use RAND for decimals, RANDBETWEEN for whole numbers in a range, and RANDARRAY to fill a block in one step. Shuffle a SEQUENCE when you need unique values, and paste the results as values when you want them to stay put.