Random numbers are useful for sampling data, picking winners, shuffling lists, creating test data and running simulations. Excel has three main functions for this: RAND returns a decimal between 0 and 1, RANDBETWEEN returns a whole number between two limits, and RANDARRAY returns a whole block of random values in newer versions. The catch is that these functions recalculate every time the sheet changes, so your numbers keep moving unless you freeze them. This guide shows each function with examples, explains how to generate random decimals, dates and names, how to make a list of unique random numbers without repeats, how to shuffle a list, how to stop the numbers from changing, and the mistakes to avoid.
Updated November 2026
In this guide
- The RAND function
- The RANDBETWEEN function
- The RANDARRAY function
- Random decimals with a set number of places
- Random dates and times
- Pick a random name or item from a list
- Unique random numbers with no repeats
- Shuffle a list of names
- Stop random numbers from changing
- Simple simulation example
- Practice problems
- Create random test data for a client sheet
- Quick recap
- Common mistakes
The RAND function
The function =RAND() takes no arguments and returns a random decimal that is greater than or equal to 0 and less than 1, such as 0.4372. Each time the worksheet recalculates, you get a new value. To get a random decimal between two numbers, use the formula =RAND()*(high-low)+low. For example, =RAND()*(10-5)+5 returns a random decimal between 5 and 10. Fill the formula down to get as many values as you need. Use RAND when you need decimals, or when you want a random sort key for shuffling a list.
The RANDBETWEEN function
The function =RANDBETWEEN(bottom, top) returns a random whole number between the two limits, including both. =RANDBETWEEN(1,100) returns an integer from 1 to 100. Negative numbers work too: =RANDBETWEEN(-10,10). To simulate a dice roll, use =RANDBETWEEN(1,6). For a random percentage between 20 and 80, use =RANDBETWEEN(20,80)/100 and format the cell as a percentage. The bottom number must not be larger than the top, otherwise Excel returns a #NUM! error. RANDBETWEEN can return the same value more than once, so it is not suitable for a list that must have no repeats.
The RANDARRAY function
In Microsoft 365, Excel 2021 and Excel 2024, =RANDARRAY([rows],[columns],[min],[max],[integer]) fills a block with one formula. For example, =RANDARRAY(5,3,1,100,TRUE) returns a block of 5 rows and 3 columns of whole numbers from 1 to 100. If you leave out the last argument or use FALSE, it returns decimals. If you leave out everything, you get a single decimal between 0 and 1, like RAND. The result spills into the neighboring cells, so those cells must be empty, otherwise you will see a #SPILL! error. RANDARRAY is faster to set up than filling down RAND or RANDBETWEEN, but it is not available in Excel 2019 or earlier.
Random decimals with a set number of places
To control the decimals, wrap RAND in ROUND. =ROUND(RAND()*100,2) returns a random number from 0 to 100 with two decimals, such as 47.38. Our guide to rounding numbers in Excel explains the ROUND function in more detail. Without ROUND, the number is stored with many more decimals than you see, and sums can look slightly different from the displayed values. For test data in a price list, round to two decimals, and for a rate, round to four.
Random dates and times
Dates are stored as numbers, so RANDBETWEEN can produce them. =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)) returns a random date in 2026, and you should format the cell as a date. For a random time of day, use =RAND() and format the cell as a time, since a fraction of a day is a time. For a random date and time together, add them: =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31))+RAND(). For working days only, wrap the formula in WORKDAY, such as =WORKDAY(RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31))-1,1), which returns the first working day on or after the random date, so weekend dates move to Monday.
Pick a random name or item from a list
To choose a random item from the list in A2:A21, use =INDEX(A2:A21,RANDBETWEEN(1,ROWS(A2:A21))). ROWS counts the items, so the formula adapts when you change the list size. In Microsoft 365, you can use =INDEX(A2:A21,RANDARRAY(1,1,1,ROWS(A2:A21),TRUE)). Each time you press F9 or edit the sheet, a new name appears. This is a simple way to draw a winner for a giveaway or to choose a person for a task. If you need several different winners, use the shuffle method below, so the same name cannot be picked twice.
Unique random numbers with no repeats
To get a list of random numbers without duplicates, such as numbers from 1 to 20 in random order, use a helper column. Put the numbers 1 to 20 in A2:A21. In B2 type =RAND() and fill down. Then sort the data by column B. Because RAND values are almost never equal, the order of column A is a random shuffle. In Microsoft 365, a single formula does it: =SORTBY(SEQUENCE(20),RANDARRAY(20)), which returns the numbers 1 to 20 in random order, with no repeats. To take only the first 5 of the shuffled list, which is a random sample of five, use =TAKE(SORTBY(SEQUENCE(20),RANDARRAY(20)),5).
Shuffle a list of names
To shuffle a list, add a helper column with =RAND() next to the names, select both columns and sort by the helper column. Each sort gives a new order, because the RAND values change after each recalculation. Copy the shuffled names and paste them as values if you want to keep the order. In Microsoft 365, =SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21))) shuffles the list in one formula. For a random sample of 5 names from the list, take the first 5 results of the shuffled list. Use this for assigning tasks, creating random teams, or selecting a test sample from a customer list.
Stop random numbers from changing
RAND, RANDBETWEEN and RANDARRAY are volatile, which means they recalculate whenever anything in the workbook changes, or when you press F9. To keep the numbers, select the cells, copy them, and then use Paste Special, Values. The formulas are replaced by fixed numbers. A keyboard shortcut is to select the cells, press F2 to edit, and then F9 to convert the formula to its value, then Enter. If you want to stop all recalculation temporarily, go to Formulas, Calculation Options, and choose Manual. Remember to switch back to Automatic, or other formulas in the workbook will not update.
Simple simulation example
Suppose you want to simulate rolling two dice 100 times. In A2 type =RANDBETWEEN(1,6) and in B2 type the same. In C2 use =A2+B2. Fill the three columns down to row 101. Then count how often each total from 2 to 12 appears with COUNTIF, such as =COUNTIF(C2:C101,7). Over many rolls, a total of 7 should appear more than a total of 2 or 12, because there are six ways to roll a 7 and only one way to roll a 2. Press F9 to run the simulation again. This kind of model is a good first example for clients who want a Monte Carlo style forecast, where inputs vary randomly and the result is a range, not one number.
Practice problems
Try these. Problem 1: generate 10 random whole numbers between 50 and 60. Problem 2: generate 5 random prices between 9.99 and 29.99, rounded to two decimals, with =ROUND(RAND()*(29.99-9.99)+9.99,2). Problem 3: build a list of the numbers 1 to 10 in random order with the helper column method. Problem 4: pick one random name from a list of 8 names. Problem 5: simulate 50 coin flips with =RANDBETWEEN(0,1) and count the heads with SUM. Check that the results change when you press F9, and then freeze one set with Paste Special, Values.
Create random test data for a client sheet
Freelancers often need realistic dummy data to test a dashboard before the client sends the real file. Build a table with 200 rows. Use =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)) for the order date, =INDEX({"East","West","North","South"},RANDBETWEEN(1,4)) for the region, =RANDBETWEEN(1,20) for the quantity and =ROUND(RAND()*90+10,2) for the unit price. Add a total column with a simple multiplication, as explained in our guide to multiplying in Excel. Once the table looks right, copy it and use Paste Special, Values, so the numbers stop changing while you build charts and pivot tables on top of them. Remember to delete the dummy data and replace it with the real data before delivery, and keep the formulas in a hidden sheet in case you need to refresh the test data later. This approach saves hours compared with typing sample rows by hand.
Quick recap
Use RAND for decimals, RANDBETWEEN for whole numbers and RANDARRAY for blocks in newer versions. Wrap RAND in ROUND to control decimals, use INDEX with RANDBETWEEN to pick a random item, and use a helper column with RAND to shuffle a list. Freeze the results with Paste Special, Values, because these functions recalculate every time.
Common mistakes
The most common mistake is to forget that the numbers change, so a report that uses random values shows different results each time it opens. Freeze the values before you share the file. Another is to use RANDBETWEEN for a unique list, which can repeat values. A third is to put the bottom limit above the top, causing #NUM!. A fourth is to use RANDARRAY in a file that will be opened in an older version of Excel, where it shows #NAME?. A fifth is to rely on Excel random numbers for security or a legal lottery, since they are not designed for cryptographic use. For ordinary sampling and testing, they are fine.
Frequently asked questions
How do I generate a random number between 1 and 100 in Excel?
Use =RANDBETWEEN(1,100).
How do I stop random numbers from changing?
Copy the cells and use Paste Special, Values, or convert each formula with F9.
How do I make unique random numbers?
Use a helper column with RAND and sort, or SORTBY with SEQUENCE and RANDARRAY in Microsoft 365.
What is the difference between RAND and RANDBETWEEN?
RAND returns a decimal between 0 and 1. RANDBETWEEN returns a whole number between two limits.
Which versions have RANDARRAY?
Microsoft 365, Excel 2021 and Excel 2024.


