Excel RAND and RANDBETWEEN Functions

RAND returns a random decimal between 0 and 1. RANDBETWEEN returns a random whole number between two values you set. Both recalculate every time the worksheet updates — which is either useful or a problem depending on what you're doing.

Syntax

=RAND()

RAND takes no arguments.

=RANDBETWEEN(bottom, top)

Parameter	Required	Description
bottom	Yes	The smallest integer the function can return.
top	Yes	The largest integer the function can return.

Basic Examples

Generate a random sales amount between $500 and $5,000 for a test dataset:

=RANDBETWEEN(500, 5000)   // returns a random integer, e.g. 2847

Generate a random score between 0 and 100:

=RANDBETWEEN(0, 100)   // returns a whole number like 73

Generate a random decimal — useful when you need a probability or a precise value:

=RAND() * 5000   // returns a decimal like 2847.334...

RAND alone gives you a number between 0 and 1. Multiply it by any constant to scale the range. Multiply and add to shift the floor: =RAND() * 4500 + 500 gives a decimal between 500 and 5000.

How RAND and RANDBETWEEN Work

RAND returns decimals, RANDBETWEEN returns integers

RAND gives you a number like 0.7341829. RANDBETWEEN(1, 10) gives you 7. If you need whole numbers, use RANDBETWEEN. If you need decimals or fractions, use RAND.

Both recalculate on every worksheet change

Every time you press Enter, edit a cell, or the workbook recalculates, every RAND and RANDBETWEEN formula generates a new value. This is volatile behavior — the numbers never sit still while formulas are active.

RANDBETWEEN is inclusive on both ends

=RANDBETWEEN(1, 6) can return 1, 2, 3, 4, 5, or 6. Both the bottom and top values are possible results.

Common Use Cases

Filling a column with test data

Enter the formula in the first row and copy it down to fill the column. Use RANDBETWEEN when your model expects integers (quantities, scores, IDs) and RAND when it expects decimals (prices, rates, probabilities).

=RANDBETWEEN(1, 100)   // random integer for each row

Simulating survey responses on a 1–5 scale

=RANDBETWEEN(1, 5)   // simulates a Likert scale response

Generating random prices with decimals

=RAND() * 99 + 1   // random price between $1.00 and $100.00

Randomly assigning employees to groups

=RANDBETWEEN(1, 3)   // assigns each row to group 1, 2, or 3

Notes & Gotchas

• Freeze the values before you use the data. Once you've generated the test data you want, copy the range and paste as values (Ctrl+Shift+V → Values, or Alt+E+S+V). Otherwise the numbers will change every time you touch the workbook.

• RANDBETWEEN can return the same number in consecutive rows. It's random, not shuffled. If you need unique values, you'll need a different approach — a helper column with RANK or the SORTBY/RANDARRAY combination in Excel 365.

• Negative ranges work. =RANDBETWEEN(-10, 10) is valid and returns integers between -10 and 10.

• RAND never returns exactly 0 or 1. The range is exclusive: the result is always strictly between 0 and 1.