How to Generate Random Numbers in Excel Using RAND and RANDBETWEEN

You need to create random data for testing, sampling, or simulations in Excel. The RAND and RANDBETWEEN functions are built-in tools for this purpose. This article explains the difference between these two functions and provides clear steps for using them.

You will learn how to generate random decimals and integers, control their ranges, and make the values static.

Understanding Excel’s Random Number Functions

Excel provides two primary functions for generating random numbers: RAND and RANDBETWEEN. Both are volatile functions. This means they recalculate and produce a new value every time the worksheet recalculates. A recalculation happens when you edit a cell, enter new data, or press F9.

The RAND function requires no arguments. It simply returns an evenly distributed random decimal number greater than or equal to 0 and less than 1. You cannot directly set its range. The RANDBETWEEN function requires two arguments: a bottom number and a top number. It returns a random integer between those two numbers, inclusive of both endpoints.

When to Use Each Function

Use RAND when you need a random decimal for probability models, fine-grained sampling, or as a seed for more complex calculations. Use RANDBETWEEN when you need a random whole number, such as for selecting a random item from a list, simulating dice rolls, or generating test IDs.

Steps to Generate Random Numbers

Using the RAND Function

Follow these steps to create random decimal numbers between 0 and 1.

1. Select the target cell
   Click on the cell where you want the first random number to appear.
2. Enter the RAND formula
   Type =RAND() and press Enter. The cell will display a decimal like 0.715243.
3. Fill the formula down or across
   Select the cell with the formula. Drag the fill handle (the small square at the cell’s bottom-right corner) down a column or across a row to copy the formula and generate more random numbers.

Using the RANDBETWEEN Function

Follow these steps to create random integers within a specific range.

1. Select the target cell
   Click on the cell for the first random integer.
2. Enter the RANDBETWEEN formula
   Type =RANDBETWEEN(1, 100) and press Enter. Replace 1 and 100 with your desired minimum and maximum numbers. The cell will display an integer like 42.
3. Copy the formula
   Use the fill handle to copy the formula to other cells and generate a list of random integers.

Making Random Numbers Static

Because the functions are volatile, your numbers will change unexpectedly. To stop this, convert the formulas to fixed values.

1. Select and copy the random numbers
   Select the range of cells containing your RAND or RANDBETWEEN formulas. Press Ctrl+C to copy them.
2. Open the Paste Special menu
   Right-click on the same selected range and choose Paste Special from the context menu.
3. Paste as values
   In the Paste Special dialog box, under the Paste section, select Values. Click OK. The formulas are replaced with their current numeric results, which will no longer change.

Common Mistakes and Limitations

Numbers Keep Changing Every Time I Edit the Sheet

This is the expected behavior of volatile functions. If you need the numbers to remain constant, you must convert the formulas to static values using the Paste Special > Values method described above. Simply changing the calculation mode to Manual under Formulas > Calculation Options is not a reliable fix for this specific need.

I Need Random Decimals in a Custom Range (Not 0 to 1)

The RAND function alone cannot do this. You must use a formula that scales its output. For example, to get a random decimal between 5 and 10, use the formula: =5 + RAND()*(10-5). This takes the RAND result, multiplies it by the range width (5), and adds the minimum value (5).

RANDBETWEEN is Not Available in My Excel

The RANDBETWEEN function is part of the Analysis ToolPak in very old versions of Excel. In all modern versions, including Microsoft 365, Excel 2021, and Excel 2016, it is a standard function. If you get a #NAME? error, check your spelling. The correct syntax is RANDBETWEEN with no spaces in the function name.

RAND vs RANDBETWEEN: Key Differences

Item	RAND Function	RANDBETWEEN Function
Output Type	Random decimal	Random integer
Default Range	0 ≤ value < 1	User-defined (inclusive)
Arguments Required	None	Two: bottom and top
Common Use Case	Statistical simulations, seeding	Random selection, dummy data
Formula Example	=RAND()	=RANDBETWEEN(1, 50)

You can now generate random decimals with RAND and random integers within any range using RANDBETWEEN. Remember to use Paste Special > Values to finalize your data set. For more advanced random sampling, explore the Data Analysis ToolPak’s Random Number Generation tool. A useful tip is to combine RANDBETWEEN with the INDEX function to randomly pick an item from a list, using a formula like =INDEX(MyList, RANDBETWEEN(1, COUNTA(MyList))).