A Guide to Generating Random Numbers in Excel

Generating random numbers in Excel is useful for far more than games. Random values power simulations, random samples for audits and quality checks, fair selections such as prize drawings or shift assignments, shuffled lists, and realistic test data for checking formulas before real data arrives.

Excel has three functions for the job. RAND returns a random decimal between 0 and 1, RANDBETWEEN returns a random whole number in a range you set, and RANDARRAY, available in current versions of Excel, returns a whole set of random numbers at once.

This guide explains each function with examples, then shows how to generate random decimals in a range, random dates, random picks from a list, shuffled lists, and random samples with no repeats. It also covers how to stop random numbers from changing and the limits you should know. A free generator at the end creates random values and shows the matching Excel formula.

Excel's Random Number Functions at a Glance

The RAND Function

The RAND function generates a random decimal number that is greater than or equal to 0 and less than 1. It takes no arguments.

The syntax is simply =RAND(). Type it in a cell, press Enter, and you might see a value such as 0.5489. Every time the worksheet recalculates, which happens whenever you edit any cell or press F9, RAND produces a new number.

Scaling RAND to a Range

On its own, RAND is rarely what you need, but it is easy to scale. To get a random decimal between two numbers, multiply RAND by the size of the range and add the lowest value. For a random decimal between 10 and 20, use =10+(20-10)*RAND().

To control decimal places, wrap it in ROUND. =ROUND(10+(20-10)*RAND(),2) returns a value such as 14.37, which is useful for generating realistic prices or measurements. Microsoft's RAND function page has further examples.

The RANDBETWEEN Function

RANDBETWEEN generates a random whole number between two values you specify, including both ends of the range. It is ideal whenever you need integers, such as a random ID, quantity, or score.

The syntax is =RANDBETWEEN(bottom, top), where bottom is the smallest integer and top is the largest. =RANDBETWEEN(1,100) returns a whole number from 1 to 100, such as 42. Like RAND, it recalculates every time the sheet changes.

Generating Random Dates

Because Excel stores dates as serial numbers, RANDBETWEEN can generate random dates too. Give it two dates as the bottom and top, and format the result as a date.

For a random date within the next 90 days, use =RANDBETWEEN(TODAY(),TODAY()+90). For a random date within a specific year, put the year in a cell, for example A1, and use =RANDBETWEEN(DATE(A1,1,1),DATE(A1,12,31)). If the result shows as a five-digit number, apply a date format from the Home tab. Our guide to Excel time and date functions explains why dates work this way.

The RANDARRAY Function

In Microsoft 365 and other current versions of Excel, RANDARRAY generates many random numbers with a single formula that spills into neighboring cells. The syntax is =RANDARRAY([rows],[columns],[min],[max],[whole_number]), and every argument is optional.

=RANDARRAY(10) returns 10 random decimals between 0 and 1. =RANDARRAY(10,1,1,100,TRUE) returns 10 random whole numbers from 1 to 100. =RANDARRAY(5,3,50,200) fills five rows and three columns with random decimals between 50 and 200. See Microsoft's RANDARRAY function page for details.

Practical Uses and Formulas

Pick a Random Item From a List

To pick one random name from a list in A2:A9, combine INDEX with RANDBETWEEN: =INDEX(A2:A9,RANDBETWEEN(1,ROWS(A2:A9))). RANDBETWEEN chooses a position and INDEX returns the item in that position. It is a fair way to choose a winner, a presenter, or who goes first.

Shuffle a List

To put a list in random order, use =SORTBY(A2:A9,RANDARRAY(ROWS(A2:A9))). SORTBY sorts the list by a column of random numbers, which shuffles it. In older versions of Excel, add a helper column with =RAND() next to the list and sort both columns by the helper column.

Random Samples With No Repeats

RANDBETWEEN can return the same number twice, which is a problem for samples and drawings. To pick, for example, 3 different names from A2:A9, shuffle the list and take the first three: =TAKE(SORTBY(A2:A9,RANDARRAY(ROWS(A2:A9))),3). The same pattern works for unique numbers: =TAKE(SORTBY(SEQUENCE(100),RANDARRAY(100)),10) returns 10 different numbers from 1 to 100.

Simulations and Test Data

Random numbers let you model uncertainty. For example, =RANDBETWEEN(80,120) can simulate daily orders, and copying a model's key output across hundreds of recalculations shows the range of likely results. Random values also make quick test data, so you can check formulas, charts, and PivotTables before real data is available.

Games and Fun

Random functions are also great for games such as a Bingo card built in Excel, team trivia draws, or randomly assigning secret gift exchange partners.

Free Random Number and Random Pick Generator

The generator below creates random whole numbers, decimals, dates, random picks from a list, or a shuffled list, with an option to prevent repeats. Alongside the results, it shows the Excel formula that produces the same kind of result.

When you copy it to Excel, you get both the live formula, which will keep changing, and the fixed values you generated here, which will not. Use the fixed values when you need a result that stays put, such as the winners of a drawing.

How to Stop Random Numbers From Changing

RAND, RANDBETWEEN, and RANDARRAY are volatile, meaning they recalculate every time anything in the workbook changes. That is useful while you are experimenting but a problem once you need to keep a result.

To lock in the current values, select the cells, copy them with Ctrl + C, then right-click and choose Paste Special, then Values, or press Ctrl + Alt + V and choose Values. The formulas are replaced with their current results, which will no longer change.

A quicker trick for a single cell is to type the formula, and before pressing Enter, press F9. Excel calculates the formula and enters the result as a fixed value.

Limitations and Considerations

Pseudo-random numbers. Excel's random numbers are generated by an algorithm, so they are pseudo-random rather than truly random. They are more than adequate for sampling, simulations, and business tasks, but not for cryptography or security.

Repeats. RANDBETWEEN and RANDARRAY can return the same value more than once. Use the SORTBY and TAKE approach above when every value must be different.

Performance. Because they recalculate constantly, thousands of random formulas can slow a large workbook. Paste them as values once you are done, or switch calculation to manual under Formulas, Calculation Options, while you work.

Reproducibility. The worksheet functions cannot be given a seed, so you cannot regenerate the same sequence later. If you need repeatable random numbers, use the Random Number Generation tool in the Analysis ToolPak, found under Data, Data Analysis once the add-in is enabled, which accepts a random seed, or paste your values so they are saved with the file.

Putting Randomness to Work

Random selection is a simple way to make decisions fair and transparent, whether you are choosing which timesheets to audit, which customers to survey, or who picks the next holiday shift. When those decisions involve your team's schedules and hours, Updoot is business management software that tracks time, schedules, and PTO in one place for $5 per user per month. You can start a free trial anytime.

Final Thoughts

RAND, RANDBETWEEN, and RANDARRAY cover nearly every need for randomness in Excel. Use RAND for decimals, RANDBETWEEN for whole numbers and dates, and RANDARRAY for many values at once. Combine them with INDEX, SORTBY, and TAKE to pick items, shuffle lists, and draw samples without repeats, and paste as values whenever you need a result to stay fixed.

Sources

Function behavior follows Microsoft's documentation for RAND and RANDARRAY.