Randomize Numbers in Excel: Methods That Work

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, use =RAND() for a random decimal between 0 and 1, or =RANDBETWEEN(1,100) for a random whole number from 1 through 100. To shuffle numbers already in a list, add a column of random keys, freeze those keys as values, and sort the whole list by that column.

What randomizing numbers in Excel means

Randomizing can mean creating new values or changing the order of values you already have. Those are different tasks: a formula that draws numbers may produce duplicates, while shuffling a list changes its order but keeps every original item. Decide which result you need before choosing a method.

Excel’s built-in random functions generate pseudorandom values. They are suitable for examples, classroom activities, simulations, and ordinary sampling, but they are not a source of cryptographically secure randomness. A random result also is not automatically unique: separate draws can return the same number.

  • Create a decimal: use RAND.
  • Create an integer within limits: use RANDBETWEEN.
  • Fill a range of cells at once: use RANDARRAY, where available.
  • Shuffle existing records: assign random sort keys and sort the entire table.

Use RAND for random decimal values

With a blank worksheet, enter the formula below in A2. It returns one decimal value greater than or equal to 0 and less than 1, such as 0.6384; the actual result varies. Fill the formula down to create a separate draw in each row.

With the formula in A2, the result is a changing decimal in the range 0 inclusive to 1 exclusive: =RAND()

To get a decimal between other endpoints, scale and shift the result. For example, with a minimum of 10 and a maximum of 20, the expression below returns a decimal from 10 up to, but not including, 20. A possible result is 16.384, although each recalculation can produce another value.

With the lower endpoint 10 and upper endpoint 20 written into the expression, the result is a changing decimal in that interval: =10+RAND()*(20-10)

RAND is useful for assigning random scores or generating decimals for a model. If you need a particular number of decimal places for display, format the cell with Home > Number and choose the desired decimal display. Formatting changes what is shown, not the underlying precision.

Use RANDBETWEEN for whole numbers

RANDBETWEEN accepts a lower and upper bound and includes both endpoints. For example, enter the formula below in A2 to draw an integer from 1 through 100. A possible result is 37; the next recalculation could return any integer in the same range.

With the lower bound 1 and upper bound 100 supplied in A2, the result is one inclusive random integer: =RANDBETWEEN(1,100)

To make a list, fill the formula down through the required rows. Five formula cells make five draws, but they do not guarantee five different values. For instance, two cells may both return 37. If repeated numbers are acceptable, this is the simplest approach.

For a different range, replace the limits. With bounds 20 and 30, for example, each result can be any whole number from 20 to 30, including 20 and 30. If the lower bound is greater than the upper bound, Excel returns an error, so check the order of the limits.

Generate several values with RANDARRAY

RANDARRAY can spill a set of random numbers into neighboring empty cells, avoiding the need to copy a formula down. In Microsoft 365 and Excel 2021, the formula below returns five rows and one column of random integers from 1 through 100. The cells contain five draws; their exact values change when recalculated, and duplicates are possible.

With an empty area beginning at A2, this formula spills five inclusive integers from 1 to 100: =RANDARRAY(5,1,1,100,TRUE)

The arguments specify the number of rows, number of columns, minimum, maximum, and whether to return whole numbers. Set the final argument to FALSE for decimal values instead. The spill range must be clear; otherwise Excel displays a spill error because it cannot place every result. In older Excel versions without RANDARRAY, enter RANDBETWEEN in the first cell and fill it across or down.

RANDARRAY is convenient for quick grids, but it does not prevent repeats. Asking for five values from 1 to 4, for example, cannot produce five distinct integers because there are only four choices. Even when the range is larger than the sample, duplicate draws remain possible.

Shuffle an existing list without losing its rows

To randomize the order of names, numbers, or complete records, use a helper column. Suppose the values are in A2:A11. In B2, enter a random key, fill it down to B11, and then convert those keys to fixed values before sorting. Freezing them matters because RAND recalculates and could change the keys while you work.

With the list in A2:A11, put this formula in B2 and fill through B11; each row gets a random sort key: =RAND()

  1. Enter the helper formula and fill it to the last data row.
  2. Copy the helper cells, then use Home > Paste > Paste Values to replace the formulas with their current values.
  3. Select the entire table, including every column that belongs to each record. Do not select only the names or numbers if other columns must stay attached.
  4. Choose Data > Sort, select the helper column as the sort field, and sort from smallest to largest. Remove the helper column if it is no longer needed.

Sorting the full table keeps each row’s details together. Selecting just one column and sorting it independently can detach names from their associated scores or other information. For a simple one-column list, you can select that column and sort it, but including headers and related data correctly is essential for larger tables. Excel for the web has the same basic formula-and-sort approach; ribbon labels and placement can vary slightly by platform.

Choose a method for unique random numbers

Neither RANDBETWEEN nor RANDARRAY promises distinct draws. If you need a random sample without replacement, shuffle a list of all eligible values and take the first required number of rows. This preserves uniqueness when the source list itself has no duplicates.

In Microsoft 365 or Excel 2021, a dynamic-array formula can return a random selection of 10 distinct integers from 1 through 100. Enter it in an empty cell. It spills 10 values, all different, in a randomized order; the selection can change on recalculation.

With an empty output area and a desired range of 1 to 100, this returns the first 10 values after random sorting: =INDEX(SORTBY(SEQUENCE(100),RANDARRAY(100)),SEQUENCE(10))

SEQUENCE builds the numbers 1 through 100, RANDARRAY supplies a random key for each, and SORTBY orders the sequence by those keys. INDEX then returns the first 10 values. This requires the dynamic-array functions used in the formula and enough blank cells for the results to spill. To sample from a custom list, the manual helper-column method works in more Excel versions: randomize the rows, then keep the first ten.

Recalculation, fixed results, and common problems

RAND, RANDBETWEEN, and RANDARRAY are volatile: Excel can recalculate them when the workbook recalculates, not only when a cell containing the formula is edited. A list may therefore change after unrelated edits or when formulas recalculate. Press F9 in Windows to recalculate; on a Mac, use Fn+F9 if the function keys are assigned to system controls. Excel for the web recalculates formulas as well.

If you want to keep a draw, copy the formula cells and use Home > Paste > Paste Values. This replaces formulas with their current displayed results. For a shuffled table, freeze the helper keys before sorting, as described above. Saving a workbook with formulas does not make the current random values permanent; the formulas remain able to recalculate when it is opened or changed.

  • Repeated values: expected with independent draws; use a shuffled unique source list if repeats are forbidden.
  • Changing results: expected from volatile random functions; paste values to lock the current draw.
  • #SPILL!: clear cells in the intended output range for a RANDARRAY or other dynamic-array formula.
  • Rows no longer match: sort the whole data table, not an isolated field, and freeze the helper keys first.
  • Unexpected decimals: use RANDBETWEEN or RANDARRAY with TRUE for integers; number formatting alone does not turn a decimal into an integer.

Randomization for testing and fairness

Random values can help build test data, rotate assignments, create simple drawing order, or select records for a casual review. Record the selection procedure and freeze the results if another person must be able to see the same sample later. A live random formula is not a permanent audit trail because its output may change.

For high-stakes lotteries, security tokens, regulated studies, or decisions that require independently verifiable randomness, ordinary worksheet formulas may not meet the required standard. Excel’s functions are designed for spreadsheet calculations, not cryptographic security or proof that a draw was unbiased. Use a process appropriate to the stakes and any applicable rules.

Frequently Asked Questions

Can Excel generate a random number without duplicates?
Not by simply filling RANDBETWEEN or RANDARRAY; independent draws can repeat. Shuffle a source list with unique entries and take the required number of items, or use a dynamic-array random sort of a sequence in a compatible Excel version.

How do I stop random numbers from changing?
Copy the cells containing random formulas and paste values over them using Home > Paste > Paste Values. This replaces formulas with the results currently shown, so later recalculation will not regenerate those cells.

Can I randomize the order of rows in Excel?
Yes. Add a RAND helper formula beside each row, fill it down, paste the helper results as values, and sort the entire table by that column. Sorting every column together keeps each record intact.

What is the difference between RAND and RANDBETWEEN?
RAND returns a decimal greater than or equal to 0 and less than 1. RANDBETWEEN takes lower and upper limits and returns an integer that can equal either limit.

Does RANDARRAY create unique numbers?
No. RANDARRAY can quickly generate a spill range of random integers or decimals, but separate outputs may match. To get unique results, randomize a list of unique candidates and select only as many entries as the list contains.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.